6

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?

  • Due to its size this is a challenging task. Two questions: Did you consider using PostgreSQL/PostGIS-DB? It's the common DB used for OSM data storage and very powerfull. Do you really need the entired OSM planet file? For smaller extracts check osm2mssql (wiki.openstreetmap.org/wiki/Osm2mssql). I do not know its capabilities for the OSM-planet-file. – zehpunktbarron Dec 3 '15 at 9:07
  • Very curious how you plan on using Planet.OSM in MS SQL! Otherwise, the tools mentioned above for use in PostGIS are the way to go... – DPSSpatial Dec 3 '15 at 23:30
1

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

|improve this answer|||||
  • Thanks for the hint @Marco_B :) Could you plz. add ArcGIS to the OSM wiki article? – Mapper Dec 5 '15 at 10:22
1

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:

https://alastaira.wordpress.com/2011/04/15/loading-open-street-map-data-in-sql-server-part-ii-ways/

Thank you @ca0v for mentioning osm2mssql, I didn't know the existence of this useful tool

enter image description here

|improve this answer|||||
0

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).

|improve this answer|||||
  • osm2mssql is on github -- source hasn't been touched for 4 years but it works with a minor tweak to the app.config. – ca0v May 19 '18 at 11:18
0

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)[1]', 'NVARCHAR(50)'),
    amenity = OSMnode.value('(tag[@k="amenity"]/@v)[1]', 'NVARCHAR(50)'),
    crossing = OSMnode.value('(tag[@k="crossing"]/@v)[1]', 'NVARCHAR(50)'),
    highway = OSMnode.value('(tag[@k="highway"]/@v)[1]', '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 CMD or PowerShell.

I also created a script that could help you import OSM maps on the fly without need of download:

https://github.com/francesco1119/OSM_to_MSSQL

Just copy the T-SQL code and paste it into SSMS and voilà.

|improve this answer|||||
  • This approach is not suited to load an entire OSM world into MS SQL, as you're downloading data from Overpass API. The only workable approach would be to use an OSM Planet file as a data source. – mmd Nov 7 '19 at 12:13
  • OK, you are right @mmd, where can I download a slice of that file? I will try to see if I can do it from a local file – Francesco Mantovani Nov 7 '19 at 12:20
  • You can find some extracts here: download.geofabrik.de - full planet is available here: planet.openstreetmap.org – mmd Nov 7 '19 at 12:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.