0

I have a table (Table1) 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 downloaded shapefiles for "Federal Electoral Districts - Canada 2015 (English)" from the link given below.

https://open.canada.ca/data/en/dataset/737be5ea-27cf-48a3-91d6-e835f11834b0

The, Shaepfile was in "3347 - NAD83_Statistics_Canada_Lambert" projection and as I dealing with Logitudes and Latitudes, I converted it to "4326 - GCS_WGS_1984" projection. I achieved this through ogr2ogr command:

ogr2ogr -f "ESRI Shapefile" -t_srs EPSG:4326 -s_srs EPSG:3347 FED_CA_2_2_ENG_Modified.shp FED_CA_2_2_ENG.shp

Then, I loaded the resulting Shapefile to Local SQL Server DB using the ogrogr command:

ogr2ogr -f MSSQLSpatial "MSSQL:server=RBANSAL\SQLEXPRESS;database=MySpatial;trusted_connection=yes" C:\Users\rbansal\Desktop\ShapeFile\FED_CA_2_2_ENG_Modified.shp -nln "Spatial_Data" -progress

Table2 Now, I need to group the Table1 locations as per the Federal Ridings loaded into the "Spatial_Data" Table above. I am using below given query for that purpose. It runs for quite a while but, eventually completes with 0 records.

select * from MySpatial.dbo.Spatial_Data s, MySpatial.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 runs for quite a while but, eventually completes with 0 records.

What is possibly wrong here?

4
  • try using latitude, longitude – Ian Turton May 14 '18 at 16:54
  • @IanTurton, Just tried that. The query ran for like 36 minutes and still no results. – ravsun May 14 '18 at 17:45
  • Try displaying the Shapefile (2) in a GIS to validate it is located where expected – JGH May 14 '18 at 18:58
  • @JGH, the Converted Shapefile (from ESPG: 3347 to 4326), shows the areas in the correct coordinates. Plus, I also checked the 'Spatial Results' in the SQL Server, and the results show correctly there within the expected longitudes and latitudes. – ravsun May 14 '18 at 19:04
1

STContains returns null if the SRIDs of the two geometries don't match.

When you create the point, the geometry::parse method assumes an SRID of 0, which makes every comparison fail.

You could try build the points specifying the SRID using the point function

select * 
from MySpatial.dbo.Spatial_Data s, 
     MySpatial.dbo.geocodes_raw g 
where ogr_geometry.STContains(
           geometry::Point(g.geocode_longitude, 
                           g.geocode_latitude,
                           4326))=1;
5
  • I tried but even after about 24 Hours, this query is still executing. – ravsun May 16 '18 at 12:30
  • @RAVIBANSAL The time it takes sounds like an indexing issue – JGH May 16 '18 at 12:34
  • I am not so sure, @JGH. In the past, I have used the same MySpatial.dbo.geocodes_raw table for grouping locations against Provincial Locations derived from Provincial Ridings Shapefiles and it worked fine (took about 6-7 hours) with the same query as I posted in my question. The only difference between when it worked fine and now is the Shapefile and the derived geometry information. Can you please check the information I have given in my question and see if I am doing it right or if I have missed something or if there is something wrong with this particular Shapefile? – ravsun May 16 '18 at 14:34
  • can you please help me here? – ravsun May 18 '18 at 12:27
  • @RAVIBANSAL using to the provided information, I don't see what else could be the issue. You could try cutting a bit of the far north so it doesn't go to lat 89.9999 – JGH May 18 '18 at 12:51

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.