1 2 3 4 Previous Next 56 Replies Latest reply on Dec 11, 2018 8:33 PM by Richard Leeke

    Help on mapping population census blocks using dynamic radius filter

    Wilfred Eze

      Warning: Forgive me if I don't use the right terms - I am very new to tableau.

       

      I am working on a project to focusing on population of people living within a selected dynamic radius distance (in km). Second part of the project is to show which of provincial road networks pass through the selected circle.

       

      What I have:

       

      1. Shape file of 2016 population census of the province (Saskatchewan, Canada) - down to dissemination block area units IDs (DBAUIDs). This shape file also has areas (in square kms) of all the DBAUID's.

      2. Shape Map of the provincial road networks - different classes of roads, town names, metro centers, etc.

       

      My main focus is to draw circles (dynamic radius) around top 9 city centers by population. And show the population living within that radius AND amount spent on maintaining road networks passing through that circle - by increasing or decreasing the radius. I found a very good example here (Richard Leeke's Super-Charged ZIP Code Radius-Finder ) but can't reproduce it as I have limited knowledge on GIS.

       

      What I have done:

      1. I was able to build circles using the lat/long of 9 cities - chosen manually (But I wish this can be automated for all cities and be able to select by filters).

      2. I was also able to change the circle radius using a parameter.

       

      I am stuck with overlaying the population files and road network files and making them work with the circles. Like I said earlier, this (workbook found here >> Richard Leeke's Super-Charged ZIP Code Radius-Finder  is exactly what I have in mind to achieve. I need help on this. Thank you for being kind enough to help me advance in Tableau. All of the definition to understand the census data are here: Table 4.12 Dissemination area boundary files record layout.

       

      Message was edited by: Jim Dehner I removed the list of "Mentioned" - see https://community.tableau.com/community/getting-started

        • 1. Re: Help on mapping population census blocks using dynamic radius filter
          Sarah Battersby

          Hi Wilfred,

           

          The big trick here, for your application, is this comment from Richard in the post you reference: 

          "the preparation of the shapes needs to be done statically outside of Tableau. Whilst that actually works OK in this case, it does mean that the analysis is locked in to a specific centre (or set of centres). If Tableau were to support direct visualisation of geometries from a spatial data source as a new mark type, the whole process could be made dynamic, with the location of the centre, scaling of the radius and generation of the overlapping segments done on the fly with spatial functions. One day, perhaps."

           

          The cool thing is that Tableau does now support direct visualization of geometries from spatial data sources including SQL Server.  In Richard's post he mentioned doing pre-processing of the data using PostGIS.  You can do that same type of analytic processing in SQL Server (FWIW, I use SQL Server Express...the free, lightweight version - also supported in Tableau) and likely use that functionality to make a dynamic viz like you are asking about in your post. 

           

          However, the functionality for having fully dynamic buffer generation or polygon-polygon or polygon-line intersections aren't features currently implemented in Tableau by itself - you would need to use something like SQL Server to help facilitate the spatial processing.

           

          At least this is where I would start for trying to tackle a problem like this that seems to involve dynamic spatial querying.

           

          -Sarah
          Tableau Research

          3 of 3 people found this helpful
          • 2. Re: Help on mapping population census blocks using dynamic radius filter
            Simon Runc

            hi Wilfred,

             

            So the option proposed by Sarah is the one I mentioned in the previous thread (utilizing SQL Server Spatial Support and Functions), and can be seen here

             

            Tableau Spatial - YouTube

             

            (go to around 47 minutes in)

             

            Let me know how you get on

            2 of 2 people found this helpful
            • 3. Re: Help on mapping population census blocks using dynamic radius filter
              Wilfred Eze

              Hi Sarah, thank you for stepping in to clarify how best to tackle this. I totally agree with you that the solution I seek lies inRichard Leeke's work, of course. But I am unable to wrap my head around the out-of-tableau preps taken in his work as he did not provide all of those steps in a way newbies like myself can get them. That is why I brought my problem here.

               

              One thing I have done is I was able to build the circles and of course using Richard's ideas I have also replicated the circle joins in an SQL and brought into tableau to join with one of the shape files using a dummy field - but it takes eternity to load, freezes my system and would never complete till I restart my system. I am thinking there might be a way out of this problem, and I have the workbook above in my question. Could you please play with what I have and show me a way, please I will appreciate that.

               

              Thanks Simon Runc, I watched the video but again Kent Marten was connecting to an already build SQL in his demo - but no idea how he prepared that SQL such that it joins with the spatial files without weighing the system resources down. Those basic ideas are what I am after. The file I have in my dataset has the areas of each polygon/census areas. But how to sum up areas that fall within a given circle of chosen radius and aggregate the population within is my challenge. Could you please show me a sample using the attached above? Thank you.

              • 4. Re: Help on mapping population census blocks using dynamic radius filter
                Richard Leeke

                Hi Wilfred

                 

                I haven't touched any of this stuff for a couple of years - but I have had it vaguely in mind to have a bit more of a look at what is possible these days with the extra spatial support in Tableau that Sarah mentions, so I will try to have a bit of a rummage around and see if I can come up with an approach that would work for you. I'll be quite happy to share details of any of the prep work that has to happen outside Tableau - I only didn't include that in the write-up of the stuff you referenced because the write-up got too long anyway.

                 

                No promises about when (or even if) I'll get back to you with anything - but it's piqued my interest so I'll hopefully get to it quite soon.

                 

                Richard

                • 5. Re: Help on mapping population census blocks using dynamic radius filter
                  Wilfred Eze

                  I'm excited already, to have you reply to this alone is a relief. Fingers crossed as I await in good faith. I do know, based on many questions on this forum that a lot of people also need help on this topic, it will be helpful to have something from the man that have so far been able to trail-blaze this very path of spatial visualization. Thank you in advance!

                  • 6. Re: Help on mapping population census blocks using dynamic radius filter
                    Richard Leeke

                    Hi Wilfred

                     

                    I've had a bit of a look at the workbook you posted and also read up a bit on all the work the Tableau team has done in the spatial world since I last had a good look at it (it was six years ago that I did that workbook with Shawn that you found - time flies!).

                     

                    I think I have a reasonable idea of what is now possible in Tableau. As Sarah said in her comment, doing the sort of dynamic analysis you want would require that you use SQL Server as the spatial data source in order to be able to make use of the spatial functions needed. From what I can see, if you can get hold of all of the data you need and get that into SQL Server I'm pretty sure you could now drive the whole thing from Tableau 'live', rather than having to do lots of spatial preparation work outside Tableau as I had to do for that viz with Shawn. The examples Kent demonstrated in that video that Simon pointed out are doing exactly that. But having said that, there's quite a bit of tricky stuff to do to get that working, and perhaps more importantly I have a few questions about exactly what you are trying to achieve and how you would want that to work.

                     

                    So let's start with the questions.

                     

                    1) The first thing is that it's not clear to me that you actually have the raw data in the form, or at the level of detail, that you will need to do the analysis you describe. As far as I can see, although you have a shape file that goes down to the dissemination block area unit level, the census data (population, etc) is at a much coarser level (census subdivision). Is that the finest breakdown you have? Is that going to be fine-grained enough for what you want to do?

                     

                    2) How would you want the calculation of population within the selection circle to work for polygons that intersect with the circle (completely inside or completely outside are easy, but what if it is half in and half out, or nearly all in or nearly all out)? In the example that Shawn and I did where we had demographic data down to the zip code level, we chose to apportion the population based on the area of the segment(s) that were inside. But that is a sweeping assumption. 99% of the population may be at one end or the other, so that can give an answer that is grossly misleading. For the purposes of the example Shawn and I did, which was to be used to help an advertising agency target who to send advertising material to for the local pizza shop (or whatever) that approximation was probably good enough. But if you're looking to make funding decisions for road maintenance, it's probably not.

                     

                    3) What level of data have you got (or what assumptions are you planning to make) about the cost of maintenance of roads? Do you have data broken down at the level of the road segments in your roads shape file? Or in some form that can be joined to that data? Whether at the level of the segments or at a higher level, you will have a similar question to number 2) to answer with respect to apportioning the costs, I expect. Maybe you could apportion it based on the proportion of the length that lies inside the circle. That sounds doable, but tricky - but may not give you what you need.

                     

                    4) You used a spreadsheet with the names and lat/lon coordinates of nine cities, but you mentioned that you want to make that more flexible. Isn't that just a matter of finding a data set that contains all the location details you need - or am I missing something?

                     

                    5) How quickly do you need the viz to work? Do you want to be able to use it in an interactive session to explore the data dynamically for an audience who are in a hurry, or will you be using it to prepare material to go in a report, say? The reason I ask is that depending on the level of detail of the data there may be quite a bit of tricky work to do to get a viz to work quickly. That could require a lot of knowledge of how to tune SQL Server spatial queries, perhaps requiring creation of spatial indexes, etc.

                     

                    Assuming that you have (or can find) the data to support the analysis and can come up with exactly what you want to do, the approach I would take would be along the following lines. This is just to give you an idea of the work involved and where the difficulties may come. It's certainly not point-and-click simple.

                     

                    Import all of the spatial and demographic data you need into a SQL Server database. You need to be make sure that all of the spatial data uses the same spatial reference id (SRID), so you may need to learn how to transform spatial files to a different SRID. There are probably a few other tricks like this which I've forgotten about because it's years since I've done this.

                     

                    Create data sources using custom SQL to select the census polygons (and fragments of polygons). It's a long time since I've done anything like this, so there may be a bit of experimenting to do to work out what needs to be in the custom SQL and what needs to use raw SQL expressions in calculated fields.

                     

                    You will need to use some of the spatial functions supported by SQL Server - probably STBuffer() to create the circle around the town centre and STIntersection() to get the fragments of polygons and roads that lie inside the circle. There is also some maths to do if you want to apportion population and road maintenance costs. Some of the maths can probably be done in calculated fields inside Tableau, but the custom SQL and/or raw SQL at least needs to return the areas/lengths of both the fragments and the complete objects so you can apportion.

                     

                    That's about as far as I can go without actually sitting down and trying it. Hopefully that at least gives you an idea of what might be possible and what the challenges you face are.

                     

                    Richard

                    1 of 1 people found this helpful
                    • 7. Re: Help on mapping population census blocks using dynamic radius filter
                      Wilfred Eze

                      Thank you for the quick follow up, yes I need to explain a bit more.

                       

                      1. I have the population blocks down to dissemination block area units, but the data itself not too fine grained, it stopped at census subdivision. We made effort to get this data at postal code level but no luck. But this can answer our question since it is about connectivity, not like some store sales or something like that.

                       

                      2. Anything that touches the circle is INSIDE. Reason being that for connectivity sake, people don't need to be living right on the roads to use the access, they just need to live 'around' there. So any block area unit touching the circle is INSIDE. Statistically this is a big assumption, but not too bad assumption for the type of study we are looking at. Perhaps I can be able to make changes when we eventually have the data by postal codes or dissemination block area units.

                       

                       

                      3. The maintenance cost data is broken down by road section (segment) numbers, category of work and description of work location and services carried out (I intend to use this as tool-tip). I have that data in a separate excel file, not yet included in the file above. I can provide that as well. I believe this can be joined using the road section numbers. For this, proportion can be used to assign which goes into or outside the circle - (in case this is difficult to determine, we can assign any section that touches a circle goes INSIDE).

                       

                      4. I created that spreadsheet separately just to enable me connect the circles - I did not know how best to achieve this without using the excel sheets with lat/long of some metros as "test". My interest is to use filter to select cities at the end. Otherwise, all of the cities of are contained in the census file.

                       

                      5. I would like the vizz to work as fast as possible. This is because we will be presenting this to executives at the end. I work as intern here and trying my best to get this done as a project. I'd really love it to be interactive, that was main reason we choose tableau.

                       

                      I will be happy if you can go through my replies and then list out the next steps for me to take. I can provide all necessary information to be able to get your help on this. I am longing for a career in this area and willing to do anything to learn, we currently have no tableau users around here so am totally depending on you guys in here. Thank you.

                      • 8. Re: Help on mapping population census blocks using dynamic radius filter
                        Simon Runc

                        hi Wilfred,

                         

                        Thanks for the clarification on those excellent questions from Richard. Like him, and Sarah I think you will need to do some of the data-modelling outside Tableau.

                         

                        Now I'm not saying this is the best option (I am more of a mapping enthusiast than an expert!), but I was intrigued how we might go about this (and I might have something similar coming up at work!). Rather than MS SQL Server, I've used a Python library called GeoPanadas. If you are familiar with Panadas, then this is a great library as it works pretty much the same way, except we can have geometry in our data frames!

                         

                         

                        #Some imports

                        import pandas as pd

                        import shapefile

                        import numpy as np

                        import geopandas as gp

                        from geopandas import GeoDataFrame

                        from shapely.geometry import Point

                         

                        #So first I brought in my points file, just as a .csv with long/lats

                        #load in Sites Master with Lon/Lat and make into pandas (just regular pandas) dataframe

                        df = pd.read_csv('G:/My Drive/Site Master - with LonLat.csv')

                         

                        #Choose the name and long/lat columns (and just first 5 rows for a sample) ...btw this is just for this test

                        df = df.iloc[0:5, [2,11,12]]

                         

                        looks like

                         

                        #create geometry and turn into geo Data Frame

                        geometry = [Point(xy) for xy in zip(df.LONGITUDE_OUT, df.LATITUDE_OUT)]

                        df = df.drop(['LONGITUDE_OUT', 'LATITUDE_OUT'], axis=1)

                        crs = {'init': 'epsg:4326'}

                        gdf = GeoDataFrame(df, crs=crs, geometry=geometry)

                         

                        looks like

                         

                        #create buffer geometry (ie. radius) so now our points are polygon's with radius 0.5 for each point

                        gdf['geometry'] = gdf.geometry.buffer(0.5)

                         

                        looks like

                         

                        #Bring in my counties polygon file (in your case the districts)

                        c_gdf = gp.read_file('D:\Downloads\GBR_adm_shp\GBR_adm2.shp')

                        c_gdf.crs = {'init' :'epsg:4326'}

                         

                        #select only the columns I need (again this is just for my test)

                        c_gdf=c_gdf.iloc[:, [6,14]]

                         

                        #use the intersect join type to workout which counties are touched/intersect with the radius

                        new_gdf = gp.sjoin(c_gdf, gdf, how="inner", op='intersects')

                         

                        #output to new shapefile

                        new_gdf.to_file('D:\Downloads\GeoPandasExperiments\BufferShape\Buffer_SR02.shp')

                         

                        Final file looks like

                         

                        To show you what this is doing, here is it in Tableau (btw this was a very quick attempt, so forgive my visuals!) but hopefully you can see what it's doing, which is similar to what you have described, where you want any district that is even touched by the radius)

                         

                        So what you could do is (similar to Richard's original post) is create a set number of radius (say 1, 3, 5, 7, 10, 15, 20 mile) and then union them together (quite easy in GeoPandas) and then (again like Richard's post) use the parameter to select which one you want to see. I've used Richard's solution at work, and did it this way to give the illusion of dynamic control over the selected radius!

                         

                        I'm on holiday for the next few days, but wanted to give you a non-SQL way. As I said there are most likey much better ways (and probably much nicer Python!) but if you are familar with Pandas then this is a great library.

                         

                        Just a interest question for Sarah Battersby when I created the Radius Polygon, I output it as a shapefile, and tried to use Tableau's intersect join on my counties Polygon file ...basically the same as I did here

                        new_gdf = gp.sjoin(c_gdf, gdf, how="inner", op='intersects')

                        I didn't get anything joined. I've used intersect to join points and polygons (very useful, btw) but not tried Polygon to Polygon before. How does Tableau's intersect work on Polygon to Polygon? (or line to Polygon for that matter). Just interested and couldn't find anything on-line. When I'm back I can send you the 2 shapefiles, if that'll help.

                         

                        Anyway hope that helps, and give you another (possible) option

                        2 of 2 people found this helpful
                        • 9. Re: Help on mapping population census blocks using dynamic radius filter
                          Sarah Battersby

                          Hi Wilfred -

                           

                          To build off of the great geoPandas example from Simon Runc I've built out a simple example in SQL Server to show how that might work.  It's just an equivalent of  'city center' and 'block' data, and doesn't include a roads dataset, but I think the query could just be modified to incorporate that (either as custom SQL to generate the data source or using RawSQL as a calculated field...) and clip the roads as needed using whatever spatial operation works best for your purpose.

                           

                          Unfortunately it's running off of my localserver, so I can't share the dynamic version - but the queries are in the response below...

                           

                          My quick example uses a Census Tract dataset from the US Census and city locations across Washington state.  I put both of these into SQL server and connected to my database in Tableau (using 2018.3).

                           

                          This is the result - buffers around city points, and selection of all tracts that intersect with any of the buffers.  The buffer size is dynamically controlled by a parameter, so you can use any value you want - no need to pre-decide what buffer distances you want to use in the calculations.

                           

                           

                          For this, it's just a simple bit of custom SQL for set up - you would edit according to your table names and what fields you wanted to return...this is just the simplified version with some quick datasets that I downloaded. 

                           

                          All it does is select the appropriate geographies for visualization (in this case it's a buffer around the city locations, the city name so that I can disambiguate the buffers/points, the geography for the Census tracts so that I can use them in the dual axis map, and the geoid so I can disambiguate those geographies as well. 

                           

                          The selection is driven by an STIntersects that limits the tracts selected to just the ones that intersect the city buffers. 

                           

                          I imagine that this isn't optimized for speed of calculations, but the datasets were small, the query was simple, and it worked for me.

                           

                           

                          The query itself:

                           

                          select

                              city.ogr_geography.STBuffer(<Parameters.buff_distance> * 1000) as city_buffer,

                              city.name as name_city,

                              tract.geography as geog_tract,

                              tract.geoid as geoid_tract

                          from

                               wa_cities as city,

                               cb_2017_53_tract_500k as tract

                          where city.ogr_geography.STBuffer(<Parameters.buff_distance> * 1000).STIntersects(tract.geography) = 1

                           

                           

                          By changing the query to use the fields of interest to you, you should be able to set up whatever you need to analyze the data for each block within distance of your city centers (e.g., sum of population, etc.)

                           

                          -Sarah

                          Tableau Research

                          1 of 1 people found this helpful
                          • 10. Re: Help on mapping population census blocks using dynamic radius filter
                            Sarah Battersby

                            Hi Simon,

                             

                            Quick answer on your polygon to polygon intersection join question - we currently only support point to polygon intersections...so that's why it wasn't working to do the polygon to polygon intersection with shapefiles...

                             

                            -Sarah.

                            1 of 1 people found this helpful
                            • 11. Re: Help on mapping population census blocks using dynamic radius filter
                              Simon Runc

                              Cheers for the quick response, I have only ever seen examples of Point to

                              Polygon ...so good to know (and good to hear the word “currently” !!)

                               

                              Awesome work on the MS SQL solution ...I still have a local instance of MS

                              SQL, so will have fun giving this a try 

                              On Wed, 5 Dec 2018 at 21:50, Sarah Battersby <tableaucommunity@tableau.com>

                              • 12. Re: Help on mapping population census blocks using dynamic radius filter
                                Richard Leeke

                                OK, thanks for all that, Wilfred.

                                 

                                I'm still slightly dubious about how well it will work with the granularity of the data, but you clearly understand the limitation and have to work with what you can get your hands on.

                                 

                                After I sent my last reply I remembered that I have in fact had a bit of a play with the new spatial capabilities in Tableau. I had access to a preview version about this time last year and actually put together some examples doing exactly the sort of thing we have been talking about (including all the apportioning of polygons where they intersect circles) connecting live to SQL Server. I had completely forgotten I'd even done that, which is a bit of a worry.

                                 

                                So I dug out the data and some of the workbooks I used on the pre-release version and found some tables with data similar to the structure you have and put a little example together to show you what is possible. Unfortunately there's no point in me sending the workbook because it has to be connected to the SQL Server database - an extract won't work because the custom SQL uses SQL server spatial functions and references parameters, so has to run live against the database: I can't just create an extract.

                                 

                                So what I'll do is include some screenshots showing what is possible and also explain the SQL I've used. I'll also include a few commands showing the mechanics of loading the spatial data into SQL Server.

                                 

                                This has turned into a long brain dump. As you'll see there's quite a lot to it and I'm bound to have missed bits. Sing out if/when you get stuck.

                                 

                                For the example I took three shape files and loaded those into SQL Server:

                                 

                                • Street Addresses for all of New Zealand: this includes point data, so I could select a few addresses to use as the centres for the analysis, equivalent to your metros (except I have 1.7 million rows, you presumably are talking tens or hundreds of rows)
                                • Road centre lines for all of New Zealand: this has line segment data equivalent to your roads file (I have 225,000 rows, you have 950)
                                • "Meshblocks" for the area around where I live: this has polygon data broken down to fairly small areas - typically a few tens of residential properties (my data has 600 of those, you have 950 census subdivisions)

                                 

                                The reason I bothered to quote the numbers of rows is that at this stage my workbook is very slow. The speed depends on the radius I select (and therefore how many intersecting meshblocks and roads are picked up). With a radius of 500 metres and three addresses included it takes two or three minutes. Increase the radius to 1 km and it takes 5 or 6 minutes. I haven't yet put any effort into trying to speed it up and it could well be that with your row counts it's much faster - but I thought I'd warn you it currently won't impress your executives too much. In fact they'd probably have left the room before the viz has redrawn!

                                 

                                Anyway, here's where I've got to. The workbook has a filter defined on street addresses, so you can choose a number of "centres" and a parameter which determines the radius around each centre to select. Currently I've got it selecting all meshblocks and road segments which intersect the circles and displaying only that portion of each meshblock and road that falls inside the circle. But it can just as easily display the entire meshblock and/or the entire road segment.

                                If you increase the radius so that the circles overlap you can see from the darker shades where the overlaps are that the overlapping portions are in fact returned multiple times - so in terms of any calculations based on population or roading costs you'd have to be aware of the risk of double-counting.

                                Selecting just one of those addresses and zooming in a bit, I've turned on mark labels for selected marks, to show that for each meshblock fragment I'm returning both the area of the fragment and the area of the whole meshblock. Similarly for roads I'm returning the length of the fragment as well as the total length of that road segment. Clearly that then lets you apportion any of the data you want to.

                                Of course you could display the entire meshblocks and/or the entire road segments, rather than truncating them at the circle:

                                For that view you really want to show the selection circle, too, but that can't be done easily because you can only have two geometries on display in a dual axis map. I'll have a think about whether there's a way to achieve that, but it may be too tricky. You can show the circle over either the meshblocks or the road segments, though.

                                 

                                Hopefully that is getting close to the sort of thing you are after. Obviously you would need to include your data and include whatever calculations you need. You'll probably need multiple views combined in a dashboard, I imagine.

                                 

                                So what did I have to do to achieve that?

                                 

                                1) Load all the spatial data into SQL Server.

                                2) Create a data source using a custom SQL connection which used SQL Server spatial functions to generate the circle, find the intersecting meshblocks and road segments, generate the fragments that fall inside the circle and calculate the areas and lengths of the fragments.

                                3) Build a viz using the resulting data source.

                                 

                                I'll expand on each of those steps. Note that there are lots of different ways of doing some of this stuff: different tools, different approaches to things. I'm not saying that any of this is the best way - just the way I've done it so I know it works. I'm sure I'll gloss over lots of things, so just fire away with questions as you go. No guarantees how quickly I'll get to them, though.

                                 

                                Loading Data into SQL Server

                                Firstly, as Sarah suggested, I'm sure you will be able to get away with SQL Server Express, which is free and runs fine on a decent desktop/laptop. I'm doing all of this with SQL Server Express on my 4 year-old laptop. You probably also want to install SQL Server Management Studio (SSMS), which lets you do things like creating a database, creating users, creating indexes on tables and performing ad-hoc queries.

                                 

                                You need to load the shape files into SQL Server as the geography spatial type, not geometry (i.e. expressed as LAT/LONG rather than a projected coordinate system). Tableau doesn't support the geometry type from SQL Server. Your shape files may already be in geography format - but they may not. When you connect directly to a shape file in Tableau it automatically converts geometry to geography, but it can't do that on the fly for you when connecting to SQL Server. There are various ways to do that conversion including using external GIS tools to convert the shape file before you load it, or loading the geometry file and then running a SQL statement using the relevant spatial functions.

                                 

                                The approach I took initially was using a set of spatial utilities called GDAL (Geospatial Data Abstraction Library). I subsequently worked out how to do it in SQL, but I'm pretty sure the only way I managed to load the data was using GDAL, so I may as well explain it all with GDAL. There are zillions of options on a lot of the GDAL commands and there seem to be lots of subtle variations between shape files, so it took a lot of trial and error to get them to load. So whilst the commands I'll give you worked for me, you may hit different issues. (If others reading this know simpler or more foolproof ways of doing any of this I'm sure we'd all like to hear about it by the way.)

                                 

                                I'll give you the GDAL commands I used to check what format they are in and to convert from geometry to geography and also the SQL statement I used to transform a table in the database from geometry to geography.

                                 

                                Installing GDAL

                                I always find it a bit hard to work out I need from the GDAL site - it's an open source project with lots of parts to it. I just download the latest version of the executables from here.

                                 

                                Getting GDAL working can also be a bit of a mission - I find the documentation a bit ho/hum. Basically you just need to unzip the downloaded package and then add a couple of directories to the system PATH and create one new environment variable. So if you unzip it all into C:\GDAL, you would need to add these to the PATH:

                                 

                                C:\GDAL\bin

                                C:\GDAL\bin\gdal\apps

                                 

                                and also create an environment variable called GDAL_DATA:

                                 

                                C:\GDAL\bin\gdal-data

                                 

                                Having done that you can then run the GDAL utilities from a windows command prompt.

                                 

                                Checking if you have geography or geometry shape files

                                Use the command 'ogrinfo' with the options '-al' and '-so' to list some details about each of the spatial layers in the shape file (generally there's just one layer). Amongst the details you'll see either an entry saying PROJCS (projected coordinate system: that means it's a geometry type) or GEOGCS (geographic coordinate system: that means it's a geographic type which is what you want).

                                 

                                Here are a couple of examples showing a shape file before and after converting to the geographic coordinate system we want. Note that if it is GEOGCS (i.e. a geographic coordinate system) but it's not GCS_WGS_1984 I don't believe that matters. GCS_WGS_1984 is the one that Tableau uses, but I'm pretty sure I have seen somewhere that the differences between different geographic coordinate systems are negligible and Tableau can cope with other coordinate systems as long as they are geographic. If in doubt, you can do the same conversion process as you need for geometry types to get it into GCS_WGS_1984. Also note that in some contexts you will see the term EPSG:4326. I believe this is another way of saying GCS_WGS_1984. I'm sure the GIS nerds just come up with all this confusion to protect their patch.

                                 

                                Here's a geometry file:

                                 

                                C:>ogrinfo -al -so "au11_region.shp

                                INFO: Open of `au11_region.shp'

                                      using driver `ESRI Shapefile' successful.

                                 

                                 

                                Layer name: au11_region

                                Geometry: Polygon

                                Feature Count: 1995

                                Extent: (1067061.210000, 4701317.175000) - (2114868.300000, 6242140.290000)

                                Layer SRS WKT:

                                PROJCS["NZGD_2000_New_Zealand_Transverse_Mercator",

                                    GEOGCS["GCS_NZGD_2000",

                                        DATUM["New_Zealand_Geodetic_Datum_2000",

                                            SPHEROID["GRS_1980",6378137.0,298.257222101]],

                                        PRIMEM["Greenwich",0.0],

                                        UNIT["Degree",0.0174532925199433]],

                                    PROJECTION["Transverse_Mercator"],

                                    PARAMETER["False_Easting",1600000.0],

                                    PARAMETER["False_Northing",10000000.0],

                                    PARAMETER["Central_Meridian",173.0],

                                    PARAMETER["Scale_Factor",0.9996],

                                    PARAMETER["Latitude_Of_Origin",0.0],

                                    UNIT["Meter",1.0]]

                                AU11: String (6.0)

                                NAME: String (100.0)

                                 

                                And here's what you get after it has been converted:

                                 

                                C:\Data\Tableau\Spatial Preview>ogrinfo -so au11_region au11_region

                                INFO: Open of `au11_region'

                                      using driver `ESRI Shapefile' successful.

                                 

                                 

                                Layer name: au11_region

                                Geometry: Polygon

                                Feature Count: 1995

                                Extent: (166.138655, -47.724046) - (178.836208, -33.958498)

                                Layer SRS WKT:

                                GEOGCS["GCS_WGS_1984",

                                    DATUM["WGS_1984",

                                        SPHEROID["WGS_84",6378137,298.257223563]],

                                    PRIMEM["Greenwich",0],

                                    UNIT["Degree",0.017453292519943295]]

                                AU11: String (6.0)

                                NAME: String (100.0)

                                 

                                Converting geometry to geography

                                If any of your files say PROJCS you'll need to run a command to create a geographic version of the shape file. Here's the command I used:

                                 

                                ogr2ogr -overwrite -t_srs "EPSG:4326" -f "ESRI Shapefile" "au11_region_geography\au11_region.shp" "au11_region_geometry\au11_region.shp"

                                 

                                -overwrite tells it to overwrite the output file if it exists

                                -t_srs specifies the target coordinate reference system

                                -f specifies that we want a new ESRI shape file and gives the path

                                the final parameter is the source file

                                 

                                Loading shape files into SQL Server

                                Having installed SQL Server you'll need to create a database. Read up on SSMS to find how to do that. You should be fine just accepting defaults.

                                 

                                The following GDAL command will load a shapefile into a new table in the database you've created.

                                 

                                ogr2ogr -overwrite -lco GEOM_TYPE=geography -f MSSQLSpatial -nln au11_region_geography "MSSQL:server=RICHARDS-LAPTOP\SQLEXPRESS;database=NZMaps;trusted_connection=yes" au11_region.shp

                                 

                                -overwrite tells it to overwrite the target table if it exists

                                -lco GEOM_TYPE=geography specifies that the layer should be created as geography (even if the source file is geography when I was trying it seemed to default the target to geometry for some reason). I also tried loading from a geometry shape file and converting to geography with this option, but that always failed with errors like "Latitude values must be between -90 and 90 degrees"

                                -f MSSQLSpatial  specifies that we are loading to a SQL Server spatial table

                                -nln gives the name of the table to create (in this case "au11_region_geography") followed by the connection string to the database - obviously you'll need to change server and database names to match yours

                                the final parameter is the source file

                                 

                                Whenever I have run that I've had this error reported - but it seemed to carry on and succeed anyway.

                                 

                                ERROR 1: Column ogr_geometry requested for geometry, but it does not exist.

                                 

                                Once the load has finished, you can inspect the new table in SSMS. Expand the list of columns and make sure that you have a geography type, not a geometry. Here are two tables loaded from the same source file with and without the -lco option:

                                 

                                 

                                If you have trouble getting it to load as geography, you might be able to load as geometry and convert it with this SQL statement (I had loaded it into a table called au11_region_geometry and used this to create a new table called au11_region_geography):

                                 

                                INSERT INTO [au11_region_geography]

                                  SELECT GEOGRAPHY::STGeomFromWKB([au11_region_geometry].ogr_geometry.MakeValid().STUnion(ogr_geometry.STStartPoint()).STAsBinary(),4326), [au11_region_geometry].au11, [au11_region_geometry].name

                                  FROM [au11_region_geometry];

                                 

                                I won't try to explain it - I can't even remember where I found it or worked it out from.

                                 

                                I don't think the GDAL utilities create spatial indexes for the geography objects they import, but I see that I have got those defined on the tables I imported last year. I'm pretty sure that is just point-and-click in SSMS - but I can't remember. We can worry about that if you get to the point where it does what you want but it needs to go faster.

                                 

                                So that's all there is to it to get the data loaded. Now the fun starts.

                                 

                                Custom SQL

                                This is getting too long and I can't afford much more time today, so I'll just give you the SQL statement from the custom SQL connection for the datasource I used and a couple of comments about the spatial functions I've used. I think the column names I've used should make it reasonably self-explanatory.

                                 

                                Basically the statement is just joining the 3 spatial tables I mentioned at the start, with the join based on a circular buffer around each street address. Note that I filtered down from the 1.6 million addresses to just my village (using the territoria (territorial authority) and location columns and then in the workbook filtered to just a very small number of addresses otherwise I wouldn't have been able to do anything in the viz because the SQL statement would have taken too long. I actually filtered to a single address in the custom SQL connection initially - even running it with all addresses in the village selected would have taken for ever. Having restricted it to one address I then added a filter in the viz which meant I could then remove the restriction from the custom SQL.

                                 

                                SELECT

                                       s.ogr_fid AS street_ogr_fid,

                                       s.ogr_geometry AS street_geom,

                                       s.id AS street_id,

                                       s.address AS address,

                                       s.house_numb AS house_number,

                                       s.road_name AS street_road_name,

                                       s.locality AS locality,

                                       s.territoria AS territorial_authority,

                                       s.ogr_geometry.STBuffer(<Parameters.Radius (m)>) AS circle,

                                       m.ID AS meshblock_ID,

                                       m.geom AS meshblock_geom,

                                       m.geom.STIntersection(s.ogr_geometry.STBuffer(<Parameters.Radius (m)>)) AS meshblock_fragment,

                                       m.AREA AS meshblock_area,

                                       m.geom.STArea() AS meshblock_area_calc,

                                       m.geom.STIntersection(s.ogr_geometry.STBuffer(<Parameters.Radius (m)>)).STArea() AS meshblock_fragment_area,

                                       m.TA11 AS meshblock_TA,

                                       r.id AS road_id,

                                       r.ogr_geometry AS road_geom,

                                       r.ogr_geometry.STLength() AS road_length,

                                       r.name AS road_name,

                                       r.other_name AS road_other_name,

                                       r.ogr_geometry.STIntersection(s.ogr_geometry.STBuffer(<Parameters.Radius (m)>)) AS road_fragment,

                                       r.ogr_geometry.STIntersection(s.ogr_geometry.STBuffer(<Parameters.Radius (m)>)).STLength() AS road_fragment_length

                                FROM dbo.nz_street_address_elector s

                                  INNER JOIN dbo.porirua_mb_wgs84 m

                                    ON m.geom.STIntersects(s.ogr_geometry.STBuffer(<Parameters.Radius (m)>)) = 1

                                  INNER JOIN dbo.roads_geography r

                                    ON r.ogr_geometry.STIntersects(s.ogr_geometry.STBuffer(<Parameters.Radius (m)>)) = 1

                                WHERE s.territoria = 'Porirua City'

                                  AND s.locality = 'Plimmerton'

                                 

                                Notes:

                                1) s.ogr_geometry.STBuffer(<Parameters.Radius (m)>) AS circle.

                                 

                                The STBuffer(radius) function returns a circular geometry object of the given radius around the s.ogr_geometry point respresenting the location of the street address. That gets returned as a column in the result set (to allow drawing the circle) and is also used in working out what overlapping meshblocks and roads to include and in generating the fragments inside the circle.

                                 

                                2) m.geom.STIntersection(s.ogr_geometry.STBuffer(<Parameters.Radius (m)>)) AS meshblock_fragment

                                 

                                The STIntersection() function generates a geometry object representing the overlap between the circle and the meshblock.

                                 

                                3) m.geom.STArea() AS meshblock_area_calc

                                 

                                The STArea() function returns the area in square metres of the meshblock. Note that the shape file already had an area column defined, but in different units.

                                 

                                4) m.geom.STIntersection(s.ogr_geometry.STBuffer(<Parameters.Radius (m)>)).STArea() AS meshblock_fragment_area

                                 

                                This is the area of the fragment inside the circle.

                                 

                                5) Same approach as steps 2) to 4) for the roads except STLength() instead of STArea().

                                 

                                6) FROM dbo.nz_street_address_elector s

                                      INNER JOIN dbo.porirua_mb_wgs84 m

                                        ON m.geom.STIntersects(s.ogr_geometry.STBuffer(<Parameters.Radius (m)>)) = 1

                                 

                                The join condition uses the STIntersects() function which returns 1 if the two shapes intersect.

                                 

                                 

                                Well that lot should keep you busy for a while. Let me know how you go and if/when you get stuck.

                                 

                                Richard

                                4 of 4 people found this helpful
                                • 14. Re: Help on mapping population census blocks using dynamic radius filter
                                  Wilfred Eze

                                  Thanks Sarah, I will take my time to study all you have here and the options from Simon and Richard and see which one I can handle. I must be frank, I have to truly start small and study up to understand many of these options. Thank you!

                                  1 2 3 4 Previous Next