11 Replies Latest reply on Nov 18, 2018 1:39 AM by Simon Runc

    Combining two data sources (spatial and lat/longs) on a map.

    DANIEL BOGESDORFER

      I'm putting together a project that depicts fracking (oil and gas) violations here in Pennsylvania that are in proximity to Class A trout streams .  With this map, I'd like to include spatial data from http://www.pasda.psu.edu/uci/SearchResults.aspx?Keyword=TROUT which depicts the streams here in Pa.  Is there a way to overlay these two maps

       

      Here is my query results from the VIOLATIONS table

       

      LATITUDE DECIMAL_NAD83LONGITUDE DECIMAL_NAD83P_VIOLATION_TYPEVIOLATION_CODE
      41.8777940000-76.3093220000278a85(a)5 - CASING AND CEMENTING - CEMENT STANDARDS - Operator failed to prevent gas flow in the annulus and use gas block additives and low fluid loss slurries in areas of known shallow gas producing zones.
      41.8777940000-76.3093220000278a86 - CASING AND CEMENTING - DEFECTIVE CASING OR CEMENTING - Operator failed to report defect in a well that has defective, insufficient or improperly cemented casing to the Department within 24 hours of discovery. Operator failed to correct defect or failed to submit a plan to correct the defect for approval by the Department within 30 days.

       

       

       

      Streams.jpg

      Thanks

       

      Daniel

        • 1. Re: Combining two data sources (spatial and lat/longs) on a map.
          Ken Flerlage

          Any chance you can attach a packaged workbook?

           

          (BTW. I'm in PA too and have always been concerned about fracking, so I'm definitely interested in seeing the results of this analysis).

          • 2. Re: Combining two data sources (spatial and lat/longs) on a map.
            Simon Runc

            hi Daniel,

             

            So a bit of an odd solution here, but it gets the job done!

             

            So first I downloaded one of the Streams Shapefiles and brought into Tableau. Next I created an Excel from the 2 rows you provided. I added this as another data connection and did this little hack to (essentially) union them together (using the "create join calculation" option)

             

             

            So far so odd...and now how to get these both in the same map (this was a feature that was recently added)

             

            I create a map using the Geometry column from Streams. I then duplicate the axis (hold ctrl and drag the longitude to the right), and in the detail I brought in both the Long/Lat fields from the Excel. The new feature allows, with this set up to combine geometry (generated Long/Lat) with actual long/lat fields.

             

            I've also used the Viz in Tool tip (may as well use as many new features as I can in one thread!) as a way to display both violations when you hover over the mark.

             

            Bit funny, but hope it makes sense and helps.

            1 of 1 people found this helpful
            • 3. Re: Combining two data sources (spatial and lat/longs) on a map.
              Simon Runc

              I second Ken's interest! They have just started Fracking in the UK (currently in pilot before, potentially, being given the green light to do it commercially). Thus far it has created several (albeit very small) earth quakes Fracking in Lancashire suspended following earthquake - BBC News ...the UK isn't on any fault lines so we very very rarely get any earthquakes

               

              Seems like a lot of trouble and cost to cling onto the fossil fuel dream for a few more years ...but always open to changing my mind on new evidence/information so interested to see what you find!

              • 4. Re: Combining two data sources (spatial and lat/longs) on a map.
                DANIEL BOGESDORFER

                Ken - I've been burning the candle on both ends the past few weeks on really learning the in's and out's of Tableau. What a great product!  This project morphed because of a "friend" of mine telling me how fracking actually helps the environment and me being me (argumentative and need to prove a point type of guy) needed to give him the "infographic" on what really happening - The Facts and Just the Facts

                 

                I've compiled a lot of data the past few nights (pdfs, excel, MSSQL) and done some data massaging.   If you or anyone else is interested let me know and we can share ideas.  I think it could turn out to be something really great.  I have a MSSQL database up on Amazon AWS that I'll eventually populate with all this compiled data. I'm a dba by trade so its easier for me to create the backend with all the tables and then just create views into them.

                 

                Hope I didn't ramble on too much.  I really do enjoy this product 

                 

                Let me know if the attachment is ok

                 

                 

                 

                • 5. Re: Combining two data sources (spatial and lat/longs) on a map.
                  Simon Runc

                  hi Daniel,

                   

                  Ah you're a DBA ...in which case you won't find the 1 = 0 join odd at all (just bad practice!)

                   

                  Also if you are using MS SQL, which can hold geometry datatypes, Tableau lets you connect to them directly and understands them. So you could probably do the union in a view, far more elegantly

                   

                  You can also use RAW_SQL pass through in the formula fields to do some even cooler stuff (using MS SQL spatial query functions). Here's a link with some information on it

                   

                  Visualize spatial data directly from SQL Server in Tableau 2018.1 | Tableau Software

                  2 of 2 people found this helpful
                  • 6. Re: Combining two data sources (spatial and lat/longs) on a map.
                    DANIEL BOGESDORFER

                    Thanks Simon.  I duplicated Ashwin Kumar and Jim Walseth's demo on Bayou Tapestry and used ogr2ogr to import data into SQL Server a few weeks ago.  I'll have to go back and look at my notes on exactly what I did.  Took me awhile but finally got it to work.

                    • 7. Re: Combining two data sources (spatial and lat/longs) on a map.
                      DANIEL BOGESDORFER

                      This is addictive.  Took the GeoJson and pushed it into Mapbox then used the map service in Tableau to bring it back in. Now I don't have to worry about querying the db or importing geo stuff.

                       

                      fish_streams.jpg

                      1 of 1 people found this helpful
                      • 8. Re: Combining two data sources (spatial and lat/longs) on a map.
                        Simon Runc

                        Very nice and yes maps are very addictive! ...I've lost countless days

                         

                        if you only need the streams (or any layer) in the image, and so don't need to "interact" with it, bringing it in via Mapbox is a very nice solution. Also means you still have 2 "interactive data layers" to add via Tableau.

                        • 9. Re: Combining two data sources (spatial and lat/longs) on a map.
                          DANIEL BOGESDORFER

                          Simon - Thinking about this on my morning run.  The only downside to the Mapbox I can see is if I want to do proximity calculations. e.g. How many violations, waste handling facilities...  are within a specific distance to a stream.  I can then filter on those variable.  Hmm?  Now I only have to make the decision - Do I go outside and shovel snow or do Tableau today

                          • 10. Re: Combining two data sources (spatial and lat/longs) on a map.
                            DANIEL BOGESDORFER

                            Just a quick note for those who'd like to bring the geoJson data into SQL Server.  This simple command will put the file into SQL server using ogr2ogr from https://www.gdal.org/index.html

                             

                            C:\Users\bogesdor\Downloads\release-1900-x64-gdal-2-3-2-mapserver-7-2-1\bin\gdal\apps\ogr2ogr -f "MSSQLSpatial" "MSSQL:server=100.1.1.1;database=JSON_DATA;trusted_connection=yes" "C:\Users\bogesdor\Downloads\Wilderness_Trout_Streams201506.geojson" -lco "GEOM_TYPE=geography" -lco 'GEO_NAME=geography" -progress

                             

                            note: if your not using integrated authentication then the documentation states -- The ODBC documentation suggests keywords UID and PWD instead of User ID and Password.

                             

                            example of going to Amazon AWS using Username and Password:

                            C:\WINDOWS\system32>C:\Users\bogesdor\Downloads\release-1900-x64-gdal-2-3-2-mapserver-7-2-1\bin\gdal\apps\ogr2ogr -f "MSSQLSpatial" "MSSQL:server=xxxx.c1eeeevw.us-east-1.rds.amazonaws.com;database=FracFocusData-November-15-2018;UID=daniel;PWD=RedRumDaniel" "C:\Users\bogesdor\Downloads\Wilderness_Trout_Streams201506.geojson" -lco "GEOM_TYPE=geography" -lco 'GEO_NAME=geography" -progress

                             

                            This will load it into a table called wilderness_trout_streams201506.  Querying the table will show this:

                             

                            maps_sql_server.jpg

                            1 of 1 people found this helpful
                            • 11. Re: Combining two data sources (spatial and lat/longs) on a map.
                              Simon Runc

                              Hmm...Physical labour in the cold, or cosy and warm with an interesting problem to investigate ...tricky one

                               

                              Daniel that's for sharing this. Although I only have a local instance of MSSQL for demo and general noodle purposes (we used ExaSol and, now, Snowflake for our main databases, at work). I did try to play with some of the new spatial MSSQL/Tableau functions a while back and the biggest challenge was loading geometry data into a MSSQL so this is really useful. There are lots of blogs, how to's and demos on all the cool spatial things you can do in MSSQL, but relatively little on how to get the data in! (and even less when on something goes wrong and why)

                               

                              Thanks again