1

I have been trying to load a shapefile available at https://www.elections.on.ca/en/voting-in-ontario/electoral-district-shapefiles/open-use-data-product-licence-agreement/download-shapefiles.html to SQL Server 2014 in the EPSG: 4326 formats.

I tried this using the Shape2SQL tool available at https://www.sharpgis.net/page/SQL-Server-2008-Spatial-Tools

I checked the "Planar Geometry" option and left the "Set SRID" field unchecked. If I check the "Set SRID" field and enter 4326 in the field, nothing gets uploaded to SQL Server. Hence, I left it unchecked.

The loaded coordinates look like this and are NOT in LAT LONG form:

ID  ED_ID ENGLISH_NA       FRENCH_NAM       SHAPE_area    SHAPE_len  geom
1   8     Brampton Centre  Brampton-Centre  4.444118E+07  32761.97   0x000000000104B6010000A...

After uploading to the database, when I checked the SRID for the imported table in SQL Server using the command:

select distinct SP_GEOMETRY.STSrid from dbo.MYTABLE

It returns "0".

Please help with following questions:

  1. Is the shapefile getting loaded into the SQLServer correctly?

  2. How do I convert the loaded data to EPSG: 4326?

Improve this question
1
  • Could a SRID of 0 not just represent the default CRS, which might already be EPSG:4326?
    – nmtoken
    Commented Sep 12, 2018 at 14:57

1 Answer 1

Reset to default
2

You can use the following:

UPDATE MYTABLE SET SP_GEOMETRY.STSrd = '4326'

To check it worked, first use SQL to verify the values were written correctly:

SELECT SP_GEOMETRY.STSrid from MYTABLE

(don't use distinct)

You might also ensure the coordinates are in WGS84 by selecting the geometry as text:

SELECT SP_GEOMETRY.STAsText()

Then, can you add the data to QGIS to ensure it draws correctly?

Improve this answer
7
  • Thanks, @DPSSpatial. But, don't I need to reproject the coordinates as well?
    – ravsun
    Commented Sep 12, 2018 at 12:39
  • What does geometry.STSrid() say?
    – Inactivated Account
    Commented Sep 12, 2018 at 14:47
  • It says, it is 4326 now. But the coordinates are still in the same form as mentioned in the Question. I need them in Lat and Long format.
    – ravsun
    Commented Sep 12, 2018 at 14:49
  • ogr2ogr should have translated them - did you the s_srs and t_srs parameters?
    – Inactivated Account
    Commented Sep 12, 2018 at 14:54
  • and - what does geometry.STAsText say?
    – Inactivated Account
    Commented Sep 12, 2018 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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