0

Via ArcMap, I imported a Feature Class into my SQL2019 Server. No issues and the polygons are displaying properly in the 'spatial results' tab when I check. Inside that feature class, there are three distinct shapes (Lets call the field tblGeo.AREA).

I have another table with LAT/LNG coordinate points (tblPoint.LAT, tblPoint.LNG).

Using the two tables (tblGeo and tblPoint), how can I determine which AREA field the coordinate falls into (if any)?

tblGeo:

Field Name Field Type Sample
GID INT 1,2,3...
SHAPE GEOMETRY 0x2569... or 0x110F...
GEOAREA VARCHAR(50) Washington, New York,...

tblPoint:

Field Name Field Type Sample
PID INT 1,2,3...
LOCATION VARCHAR(100) White House
LAT DECIMAL(9,6) 38.897957
LNG DECIMAL(9,6) -77.036560

Desired Output

PID Location Lat Lng GeoArea
1 White House 38.897957 -77.036560 Washington
2 Empire State Building 40.748817 -73.985428 New York
... ... ... ...
rak11
  • 123
  • 1
  • 15

1 Answers1

1

Sample input and output data would be nice.
You'll need to convert LAT and LNG to a geometry point.
Assuming LAT and LNG are DECIMAL(9, 6)...

select g.name as AreaName
, p.name as PointName

from tblGeo g
  right outer join tblPoint p on g.AREA.STContains(geometry::Point(p.LAT, p.LNG, 0)) = 1

I could check my work if you provided sample data.

dougp
  • 2,810
  • 1
  • 8
  • 31
  • I have added some table samples. Thank you for the reply. – rak11 Oct 08 '21 at 16:56
  • So, did it work for you? I can't test because the data you provided is not valid. For example '0x2569...' is not a geometry. Plus, your "sample" indicates you are using geometry, but your question says you're using geography. Maybe create a db fiddle. – dougp Oct 11 '21 at 17:33
  • It did not work. The Feature Class data was imported through ArcMap and exists in the database as a GEOMETRY type, so I only assume that it imported correctly. There are some functions that work (ie: tblGeo.SHAPE.STCentroid() will give me the centroid of each geometry area). But can't figure out how to do the joins just yet ... – rak11 Oct 12 '21 at 13:17
  • I updated my answer. If you expect me to test this, please provide valid DML to generate sample data. – dougp Oct 12 '21 at 15:45
  • Got it to work using your code. The only missing part was the " = 1" in the join. Thank you for all the help. – rak11 Oct 15 '21 at 15:43