I am trying to download the entire dataset of OSM and placing into a MS SQL Database.
Does anyone have any ideas of how to go about that?
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.Sign up to join this community
The above answer is not true, there is an importer for Microsoft SQL Server: ESRI's "ArcGIS Editor for OpenStreetMap", which requires ArcGIS and consists of an ArcGIS ModelBuilder toolbox. You need to download an *.osm XML file (e.g. from Geofabrik), and use the Load OSM File tool part of this toolbox. I don't recommend to use either of the two toolbox included download options, as they unfortunately have issues. Get the osm data file from Geofabrik instead.
However, with the current implementation, importing the entire planet will probably take a few months, although usage of very large 2TB SSDs or proper disk raids, can probably cut back on the time.
The biggest chunk I ever imported in a geodatabase (to a File Geodatabase admittedly, I only imported to a SQL Server Express database with its 10 GB limit), was the DACh extract of Geofabrik. That took maybe 3 weeks or so against a single 4TB hard drive. Note however, that due to the type of processing needed to resolve OSM multipolygons (which the tool does well), it requires many "random" reads against the tables storing nodes and ways to collect all the pieces of which a multipolygon may consist, thus making SSDs considerably superior for this importing process. I really wouldn't recommend trying to import the planet against a single harddrive, even if it would fit...
I know ESRI is working on trying to improve import times, but when that will come to fruition, I don't know.
If you want to import to regular SQL Server, note that you will also need an ArcGIS for Server license, which has its cost. If you just need a smaller extract that fits within the 10 GB SQL Server Express limit, and you have an ArcGIS for Desktop license (even a Home Use license will do if non-commercial application), you can run your own Personal ArcSDE, a.k.a. Desktop Geodatabase, a.k.a. ArcSDE Personal Server. This is without additional costs, as it is part of any ArcGIS for Desktop Standard and Advanced license (but not Basic). I have described how to install and use it in detail in this ESRI forum thread post: https://geonet.esri.com/message/118404#118404
You cannot import the whole world because is too big for SQL Server. But if you filter it with Osmosis and you have a decent size file you might reach your goal. An OSM file is a mere XML file. It can be imported but you first need to create the table correclty:
Currently ther is no importer to use OSM data in MS SQL Server. You can start developing an custom solution, but as the others mentioned, PostgreSQL is the recommend way to deal with geodata (esp. at global scale).
Go to this link http://download.geofabrik.de/ and download one of the files.
Open SSMS and paste this query; of course on line 5 you have to set the directory where you have downloaded and unzipped the file:
DECLARE @TableName as NVARCHAR(35) = 'Cuba' -- declare table name DECLARE @XmlFile XML SELECT @XmlFile = BulkColumn FROM OPENROWSET(BULK 'C:\Users\franc\Desktop\cuba-latestt.osm', SINGLE_BLOB) x; DROP TABLE IF EXISTS dbo.Cuba -- drop table if exist CREATE TABLE Cuba ( -- Name here [id] NUMERIC NULL, [visible] CHAR(5) NULL, [version] NUMERIC NULL, [changeset] NUMERIC NULL, [timestamp] datetime2 NULL, [user] NVARCHAR(50) NULL, [uid] NUMERIC NULL, [lat] FLOAT NULL, [lon] FLOAT NULL, [name] NVARCHAR(50) NULL, [amenity] NVARCHAR(50) NULL, [crossing] NVARCHAR(50) NULL, [highway] NVARCHAR(50) NULL ) INSERT INTO Cuba -- name here (Id,visible,[version],changeset,[timestamp],[user],[uid],lat,lon,[name],amenity,crossing,highway) SELECT id = OSMnode.value('@id', 'NUMERIC'), visible = OSMnode.value('@visible', 'CHAR(5)'), [version] = OSMnode.value('@version', 'NUMERIC'), changeset = OSMnode.value('@changeset', 'NUMERIC'), [timestamp] = OSMnode.value('@timestamp', 'datetime2'), [user] = OSMnode.value('@user', 'NVARCHAR(50)'), [uid] = OSMnode.value('@uid', 'NUMERIC'), latitude = OSMnode.value('@lat', 'FLOAT'), longitude = OSMnode.value('@lon', 'FLOAT'), [name] = OSMnode.value('(tag[@k="name"]/@v)', 'NVARCHAR(50)'), amenity = OSMnode.value('(tag[@k="amenity"]/@v)', 'NVARCHAR(50)'), crossing = OSMnode.value('(tag[@k="crossing"]/@v)', 'NVARCHAR(50)'), highway = OSMnode.value('(tag[@k="highway"]/@v)', 'NVARCHAR(50)') FROM @XmlFile.nodes('/osm/node') AS OSM(OSMnode);
In the example I'm using the file
cuba-latestt.osm which unzipped has a size of 631 MB. After a good 10 minutes of importing on an old laptop the query created the table
Cuba with a weight of 427 MB.
So in the end is possible to import easily any
.osm maps to SQL Server without need of any external tool.
Keep in mind that I haven't tried to import files of a few GB but you can give it a try and let me know if you have any problem.
There shouldn't be any timeout and as long as there are resources the importation should work. If it fails you can try to pull the file through
I also created a script that could help you import OSM maps on the fly without need of download:
Just copy the T-SQL code and paste it into SSMS and voilà.