3 Replies Latest reply on May 26, 2016 8:52 AM by Tom W

    Mapping from Two Different Tables

    Seth Rosenstein

      I have been trying for a while to plot two different sets of lat&long on a map in different colors. I have a lot of tables that are joined together on their specific ID. Deliveries is the main table and each row is one delivery with its information. Lots of dimensions in Deliveries are IDs. Each ID in the Deliveries table is joined to the table that provide detail to that ID. An example is Deliveries will have Delivery_12345, Store_ID 78 and address_ID 98765. Store_ID is then used to join the Stores table, which has Store name, location etc stored. Address_ID is joined to the Address table, which has the delivered_latitude & delivered_longitude. The Address table does not have store location data.

       

      What I want to be able to do is plot on a map all deliveries in one color and all stores in a different color. Using store_ID as a color makes all deliveries from that store a specific color, which I don't want. The problem I'm running into is the delivery locations are in the Address table store locations are in Stores table. I also tried a dual axis but a map does not generate. Attached is a example work book of my issue and the excel I used for the example.

       

      Is this possible?

       

      Thanks,

      Seth

        • 1. Re: Mapping from Two Different Tables
          Tom W

          Is your original source Excel or something else?


          The easiest way to do this would be to create a combined dataset which looks like;

          PointType, Latitude, Longitude,DeliveryId,AddressId,StoreId

          Address,39.922092,-75.162275,12345,98765, null

          Store, 39.913567, -75.173390, 12345,null,78

           

          You can achieve this using a union / custom SQL, but it will depend on the source.

          • 2. Re: Mapping from Two Different Tables
            Seth Rosenstein

            Hi Tom,

            My data is in Postgres. I just used Excel for sample data because I can't share the real data.

            • 3. Re: Mapping from Two Different Tables
              Tom W

              The fact it's in postgres is excellent.

              Either write custom SQL to union the ADDRESS and STORE records into one long resultset or create a view in postgres to do the same and connect to that.

               

              I.e.

              SELECT 'Address' as PointType, a.Lat as Latitude, a.Long as Longitude, d.DeliveryId, a.AddressId,null as StoreId

              FROM Delivery d

              JOIN Address a ON d.Address_id = a.Address_id

              UNION ALL

              SELECT 'Store' as PointType, s.Latitude, s.Longitude, d.DeliveryId, null as AddressId, s.StoreId

              FROM Delivery d

              JOIN Store s ON d.Store_id= s.Store_id

               

               

              Then you'll end up with a dataset like this;

               

              PointType Latitude LongitudeDeliveryIdAddressIdStoreId
              Address39.92209-75.16231234598765
              Store39.91357-75.17341234578
              Address39.95032-75.17144444498766
              Store39.94672-75.165744444479

               

              (Record 3 and 4 I created myself to expand on the example).

               

              See the attached representation in Tableau.