NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum

I have some OSM data downloaded by someone else. The geom is in text e.g. POLYGON ((5585434.5396439433 2454059.8880860955, 5585440.2817584425...

I imported the data into SQL Server 2008 R2 and I need to convert the OSM text data to Geometry data in SQL Server using the OGC methods provided by the Microsoft server.

Has anyone done this already and could you give me some pointers? I'm new to SQL spatial so keep it simple.

I think my first task is to figure out the SRID that they source data is in. The POLYGON above is somewhere near -120 long, 35 lat +/- 2. I want the final geom object to have a lat/long SRID 4236.

What SRID would have been used on the initial export from OSM? The text above could have been the result of someone changing the OSM data so I'll have to research that.

Thanks!

asked 17 Jul '13, 19:02

rheitzman's gravatar image

rheitzman
1111
accept rate: 0%


OSM data will nearly always be in either WGS84 (ESPG:4326 - I presume 4236 in your question is a typo) or Spherical Mercator (Google Maps) projections. The values in your POLYGON sample appear to be in the latter (EPSG:900913 or EPSG:3857 ) as they are clearly not in lat/lon units and are most likely to be in metres.

The POLYGON data is in WKT format, so you just need a function which converts WKT to the internal geoemetry representation. A 5 second google search suggests this might be what you need: STGeomfromText()

permanent link

answered 17 Jul '13, 19:28

SK53's gravatar image

SK53 ♦
28.1k48268433
accept rate: 22%

Thanks!

FYI Other readers be aware that the spatial extension names are case sensitive in SQL Server - something we are not used to. STGeomFromText()

(18 Jul '13, 20:15) rheitzman
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×5
×1
×1

question asked: 17 Jul '13, 19:02

question was seen: 2,787 times

last updated: 18 Jul '13, 20:15

NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum