1

I have a table 'geocodes_raw' of locations with Latitude and Longitude values.

Loc_Nm || geocode_latitude || geocode_longitude
   A   ||    43.43044      ||    -80.0910793
   W   ||    43.9114523    ||    -80.0911329
   X   ||    43.9209011    ||    -80.091323
   Y   ||    43.9083203    ||    -80.0913039
   Z   ||    43.914577     ||    -80.091198

I have a geometry table with area names (shown below) which has been converted from Shapefile through ogr2ogr with following command.

ogr2ogr -f MSSQLSpatial "MSSQL:server=RBANSAL\SQLEXPRESS;database=MySpatial;trusted_connection=yes" "C:\Users\rbansal\Downloads\ELECTORAL_DISTRICT.shp" -nln "ELECTORAL_DISTRICT_NEW" -lco PRECISION=NO -progress

![Table2 I need to group the Table1 locations into specific regions as per Table2. I am using a query utilizing below-mentioned snippet.

select * from MyDB.dbo.Spatial_Data s, MyDB.dbo.geocodes_raw g 
where ogr_geometry.STContains(geometry::Parse('POINT(' + CAST(g.geocode_longitude AS VARCHAR(20)) + ' ' + CAST(g.geocode_latitude AS VARCHAR(20)) + ')'))=1;

It returns zero records.

Question Updated:

But, when I load the same shapefile through shp2sql.exe tool. I get the following table. Please, note the difference in the beginning text under the geom column. enter image description here

This time, the same query works and gives me correct results.

select * from MyDB.dbo.Spatial_Data s, MyDB.dbo.geocodes_raw g 
where ogr_geometry.STContains(geometry::Parse('POINT(' + CAST(g.geocode_longitude AS VARCHAR(20)) + ' ' + CAST(g.geocode_latitude AS VARCHAR(20)) + ')'))=1;

What's going wrong when I am loading the shapefile through ogr2ogr

  • I projected the shapefile from 5320 to 4326 and then loaded into SQL Server. Do you mean to say the projection needs to be 3347 and not 4326? – RAVI BANSAL Sep 12 '18 at 18:05
  • Sorry for being a noob. I have Electoral District boundary shapefiles (with EPSG: 5320) from elections.on.ca/en/voting-in-ontario/…. I need to group certain locations (for which I have lat and Long values in a separate table) within those ED boundaries. Which projection do I need to have my shapefiles in? Is my query for Lat and Long with Ogr_Geometry, correct? – RAVI BANSAL Sep 12 '18 at 18:35
  • You should maybe turn your LAT and LON columns into a GEOMETRY column, then you could use the STIntersects function to do this, I believe... – DPSSpatial Sep 13 '18 at 22:02
  • Hi @DPSSpatial, I have got it to work through the shp2sql.exe tool, But, now this has raised a new question...what's going wrong when I am loading the shapefile through ogr2ogr CLI. I have updated my question. Please check and share your inputs. – RAVI BANSAL Sep 14 '18 at 15:18
  • What does STSrid and STAsText report for both tables? – DPSSpatial Sep 14 '18 at 15:20

Your Answer

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

Browse other questions tagged or ask your own question.