CODEWITHSUNDEEP
sqlsql-servergeospatialspatial
Cliff
Cliff

Reputation: 31

Return the polygon that lat long resides within using SQL Spatial Data types (STIntersect/STContains etc)

I have researched this for two days now and cannot find anything that works for me, it may be my database design, but I think it's designed ok.

I have a table called "post_codes_new", it contains the following columns;

id (int)
post_code (varchar, example: "0612")
post_code_name (varchar, example: "Henderson")
latlongs (geography data type - forming a polygon)

What I am attempting to do is query this table using a lat/long point to return any post_codes record that contains that lat/long (any record that the lat/long falls within the boundaries of).

Here's what I've been working on;

DECLARE @point geography;
SET @point = geography::STGeomFromText('POINT(174.94280000 -36.75000000)',4326)
select post_code
from dbo.post_codes_new
WHERE latlongs.MakeValid().STContains(@point) = 1;

No matter what lat/long point I enter here, my query returns me 931 of the 1081 records in that table and it takes 22 seconds.... It should only return one record. And I'm hoping for way better performance of course.

I'm trying to achieve this;

['PSEUDO-CODE' QUERY OF WHAT I'D LIKE TO ACHIEVE]
SELECT post_codes.* FROM post_codes WHERE latlongs.STIntersects(myLatLongPoint) = 1

The examples I have looked at (on this site and elsewhere) all seem to work with polygons that are defined within the query (ie, the points are listed in the query body explicitly). I have seen a few that look at records containing polygon boundaries, but none have worked for me.

Thanks.

Upvotes: 2

Views: 580

Answers (1)

Cliff
Cliff

Reputation: 31

Huge thanks to @BenThul and @TomC. It turned out that indeed, my points had been entered in a clockwise direction, which wraps the "rest of the world excluding your polygon" and creates that as a polygon area - hence the 931 out of 1081 records being returned in my query.

So I took my existing polygon points (that I had stored as a nvarchar(MAX) string, alongside my geography column) and reversed them, then over-wrote my geography column with those reversed values. I then ran the queries on a few known address/postcode combinations and it worked perfectly. A single postcode was returned per query, as expected. Brilliant!

It takes (consistently) 16 seconds for the query to run, but I'll work on that speed problem as a separate issue.

Thank you guys.

Upvotes: 0

Related Questions