Amazing how you articulated all these to help me. Please give me some really good time to follow these steps one at a time. I will stay close to the thread till I arrive at a solution. Thank you for your time Richard. Wow!
Thank you Simon, I will get to all of these and see which I can make something out of. So many great ideas here!
2 of 2 people found this helpful
I just have to say that Richard Leeke's reply here is awesome! I agree that GDAL and using ogr2ogr is a good way to go for importing data into SQL Server. One thing I tend to do because I've periodically had issues with the geography coming in directly from the ogr2ogr import is that I just create the geography from geometry once the data is in SQL Server. I use a trick from an old MSDN blog (Working with Invalid Data and the SQL Server 2008 Geography Data Type, Part 1b – Spatial Ed's blog ) - I think that Richard and I may be pulling inspiration from the same place with our SQL to do the conversion.
I also wanted to briefly defend the GIS nerds out there with respect to the cryptic EPSG codes... it's only sort-of the GIS nerds' fault that we have so many ways of defining coordinate systems. EPSG is the European Petroleum Survey Group so it's a special group of spatial nerds that established the codes, but they are pretty useful for clarifying which coordinate system is being used (and having fewer characters to type in... I'd rather type in 4326 than World Geodetic Survey of 1984).
Easy way to get the EPSG codes if you ever need them -there is a great lookup at http://epsg.io/
When working with shapefiles, I'll often open the .prj file (the part of the shapefile that defines the coordinate system) - it's just a text file so open it with your text editor of choice - and grab the long name for the coordinate system, and then go to epsg.io to get the corresponding code.
Databases like SQL Server, PostgreSQL, etc. will want the EPSG code and not the long-form name / details on the specific coordinate reference system.
It also makes you look pretty cool when you can just spout random EPSG codes from memory. "Oh, yeah, that's definitely 3857" then give a knowing nod to all in the room.
Hi Richard, please help explain these to me.
1. I downloaded the GDAL Library from there link you shared, although there were several files there not sure if I got the right one. I have two folders inside the extract (bin and doc, with several word files - licenses I guess). Was this what you wanted me to get?
2. I am stuck with running the GDAL commands, each time returns command is not recognized. Can I get a help understanding where to start with the commands?
3. Not sure if you know this, but my datasets are in several shape files. For instance, there is a saparate file with population data by CensusSubDivision (not very granular), another by Dissemination Area Block Units (more granular), and many other census shape files each at different aggregations. And again, the road shape file is a separate one too. You example appear to be using only one shape file. How do I reconcile this?
I have installed Microsoft SQL Server Management Studio, once I understand the command and conversion step done I will then get busy with the SQL side of things. Thanks for clarifications on the above.
1 of 1 people found this helpful
I'll jump in quickly on your questions 1 and 2 regarding installing GDAL and running ogr... I suggest looking at the OSGeo4w download (OSGeo4W)
This will include the OSGeo4W shell and GDAL/ogr. You can also include QGIS (open source GIS software) when you do the OSGeo4w installation - this is a helpful tool for any processing that you might want to do with the spatial files before you import them to SQL Server.
I use the OSGeo4w shell for running ogr2ogr to import shapefiles into SQL Server tables.
Thanks for the feedback, Sarah - and the explanation about all the cryptic spatial codes. I kind-of knew there were good reasons for all the different variants - but it does make things confusing for those of us who just dabble in this stuff. I also suspect that you're right about the SQL for doing the geometry to geography conversion - I vaguely remember the name Spatial Ed's blog - but it was ages ago I did that stuff.
Thanks also for the pointers on what looks to be a more usable release of GDAL than the one I've been using - I just stuck with the one I picked up years ago when I was doing those utilities for importing shapes into custom geocoding, but it sounds as if things have moved on. I'll take a look.
It sounds as if the GDAL version Sarah pointed you at is probably easier to set up and use, so I suggest you have a go at that first. As I said, there are all sorts of different builds and variants of that open source project and I just stuck with one I found years ago. Let me know if the one Sarah pointed you at doesn't work for you and I'll give you more detail on the way I know.
Yes, I realised your data set had multiple shape files. So did mine. I imported 3 shape files: one each for the road centre lines, the street addresses and the meshblocks in my example. It's just that in the instructions for using GDAL I only gave one example of the commands (and in fact I don't even think the example I gave was one of those three - it was just one that I had lying around in a Windows command file).
And by the way, I've had a bit more of a play with the example I did yesterday and sorted out the speed issues - it's now nice and snappy. I just had to define a few indexes on some of those big tables.
I've also come up with an (extremely bodgy!) way of showing the centre and boundary of the selection circle. It's so bodgy I'm not even going to explain how I did it at the moment to avoid confusing you further, but I'll include a screenshot as a teaser so you know what is possible. I think this is pretty much the ideal visual effect you are probably after: the polygons are not clipped because anything touched by the selection circle is included, whereas the road segments are clipped because you said you probably want to apportion the costs.
Keep us all posted on your progress!
I know this question may sound dumb Sarah, but like I said I am totally new to anything involving commands and SQL, have always been comfortable with click and drag data analysis tools before coming over to Tableau. I have this OSGeo4W downloaded and installed and could open the command window. But each time i try to run the ogrinfo -al -so 'filename' command I get an error message saying "unable to open datasource 'filename' using the following drivers." Then with list of various drivers on my system below the error message.
I am sure the error is from my end, probably not doing the right thing. Could you point me to resources on running these commands or how to fetch my files into the command or explain for me briefly? I do not want to bother Richard on this because there is enough bugging from me awaiting him once I get past these beginning steps, apologies in advance Richard. Thank you.
I eventually got the paths set up and now it reports FAILURE: No data source specified. Which locations do I need to have my shape files saved before running commands? I just need help to wrap my head around these.
1 of 1 people found this helpful
Don't worry about bothering me Wilfred, I'm quite happy to answer quick questions. And I always say there's no such thing as a dumb question: if you're stuck, you're stuck. The only thing dumb about a question is not asking it. And by the way, I'm in New Zealand, so you won't see much sign of life from me during your mornings (8am Saturday here now).
As you suspect, I think the issue is just that you haven't fully specified the path to the shape file. There are a few ways of doing that and the OGR commands accept a few options and try to be clever because they support all sorts of different spatial file types with their own conventions about what files have to be where and you don't have to tell them what file type you are opening, they try to work it out for themselves. So that error with the list of drivers is telling it tried opening whatever you said with the drivers for all the types it supports.
Probably the easiest way is just to change the working directory in the OSGeo4W command shell to the directory holding your shape files, like this:
cd <full path to your shape files>
Then run the ogrinfo command, specifying the full name of the actual shape file (*.shp) - since an ESRI 'shape file' is actually a collection of a few files and it recognises some but not all of those - and the .shp will work. Like this:
ogrinfo -al -so CensusSubdivision.shp
Hope that gets you moving.
By the way, the installation Sarah suggested will have taken care of setting the paths for you when you run the OSGeow4W shell - so you shouldn't have needed to set up the paths in the way I said. But it won't be doing any harm - the fact that you are getting those error messages confirms that the OGR utilities are actually working, I think.
Good, your last comment was helpful it got me working! I just ran the ogrinfo -al -so "path/filename" and it worked. I can see both PROJCS and GEOGCS as in your example. Time to get started . I hope to visit NZ someday, It's 1.25pm here on -12 degree centigrade. Frozen!
Hi Richard, I am stuck in this line (ogr2ogr -overwrite -t_srs "EPSG:4326" -f "ESRI Shapefile" "au11_region_geography\au11_region.shp" "au11_region_geometry\au11_region.shp").
I have my road shape file inside this folder C:\users\wilfredng\documents\Roads as "Highways.shp"
How do I write the ogr2ogr line above to reflect my file locations? I understood your file name is "au11_region", but not sure what the part "au11_region_geography and au11_region_geometry" should be in my case. Could you please clarify?
1 of 1 people found this helpful
If you're trying to do the step where you are actually copying the shapefile into your SQL Server database, this is the syntax I normally use (as Richard noted earlier, there are about a billion different options for the ogr2ogr command, this is just what I tend to use for simple imports).
This takes a shapefile in my temp folder and drops it into a database called "spatialtest" that I have created in my SQL Server Express instance. Your specific settings for server location, database name, and shapefile location will vary, of course...
ogr2ogr -f "" "=localhost\==yes" "c:\temp\" - "EPSG:4326"
Thanks Sarah, no I am trying to convert geometry file to geography file instead, that comes before copying to Sql server database. Any help on that please?
1 of 1 people found this helpful
Ah, I just import the shapefile as geometry and then do my conversion in SQL Server. So, the whole process for me is like this… it may not be as efficient as Richard’s method, but it’s just the way that makes sense to me (because I really don’t use SQL Server very much…):
1. Import the shapefile
ogr2ogr -f "MSSQLSpatial" "MSSQL:server=localhost\SQLEXPRESS;Database=spatialtest;Trusted_Connection=yes" "c:\temp\myShapefile.shp" -a_srs "EPSG:4326"
2. Create the geography column (you can name it whatever you want, I’ve just used ‘geog’ below)
alter table myShapefile
add geog geography;
3. Populate the geography column
set geog = ogr_geometry.MakeValid().STUnion(ogr_geometry.STStartPoint()).STAsText()
I don't think it's a question of one way being more efficient than the other (though on reflection your way looks simpler, Sarah). As I said in my post, I was just recording a particular set of steps I had done that had worked, but there are lots of ways to achieve this. In fact I was just recording the way for which I'd actually created a command file to record what did (a year ago when I was playing with this stuff). I can't remember which way I tried first and what issues I hit with each, but if Sarah's approach generally works for her let's stick with that way.
So just to be absolutely explicit, with Sarah's way you can skip my step about converting to a shapefile in Geography format and load each of your shapefiles directly into SQL Server with the ogr2ogr command that Sarah has given you. From what I've seen that will always result in a geometry rather than a geography column in the table in SQL Server, even if you loaded from a shapefile in geography format (which seems odd to me - is that what you find, Sarah?). Anyway, you can easily check by looking at the column definitions in SQL Server Management Studio once the table has imported. If you see a geometry column then you'll need to alter the table to add and populate new geography column, as per Sarah's steps 2 and 3.