Points inside polygons - SQL Server T-SQL + geo

Question

I have 2 tables, one containing a bunch of polygons like this

CREATE TABLE [dbo].[GeoPolygons](
    [OID] [int] IDENTITY(1,1) NOT NULL,
    [Version] [int] NULL,
    [entity_id] [varchar](200) NULL,
    [Geometry] [geometry] NULL
)

and one containing a bunch of nodes (places) like this

CREATE TABLE [dbo].[GeoPoints](
    [point_id] [int] NOT NULL,
    [point_name] [varchar](40) NULL,
    [latitude] [decimal](8, 6) NULL,
    [longitude] [decimal](9, 6) NULL,
    [point_geometry] [geography] NULL
)

These tables came from disparate sources, so one has a geography field type and the other has a geometry field type.

What I want to do - which is very possible within my GIS, but I want to do it in T-SQL for a variety of reasons - is find out which nodes are inside which polygons.

Is the first step to match the geo-field types? geometry-to-geometry or geography-to-geography?

Or can that be done on the fly?

My ideal output would be a 3 field table

CREATE TABLE [dbo].[Points2Polygons](
    [match_id] [int] IDENTITY(1,1) NOT NULL,
    [entity_id] [varchar](200) NOT NULL,
    [point_id] [int] NOT NULL
)

and be able to update that on the fly (or perhaps daily) when new records are added to each table.

I found this post but it seems to deal with a single point and a single polygon, as well as WKT definitions. I don't have WKT, and I have thousands of polys and thousands of points. I want to do it at a larger scale.

How do I do it in a T-SQL query?

Server is running SQL-Server Web 64-bit V15.0.4138.2 on Server 2019 Datacenter.

TIA

FWIW, you can get the WKT by using `.ToString()` on either the geometry or geography data. I'd suggest re-evaluating the decision to store the points as geometry though - they're clearly points on a globe and should be stored as geography regardless of provenance. — Ben Thul, Oct 01 '21 at 19:49
The points are already stored as geography. Its the polygons that are stored as geometries. I guess they could be converted safely using a WGS84 projection. If so, how would I progress towards my desired solution? — Feargal Hogan, Oct 03 '21 at 09:10
I had your points/polygons backwards but the conclusion is the same - store geography data as geography, not geometry. I'll write up a little something below to help with your conversion. — Ben Thul, Oct 03 '21 at 15:03

score 0 · Answer 1 · answered Oct 03 '21 at 15:34

From the comments above, here's a proposal to convert the Geometry column in your GeoPolygons table. As with anything like this where you have data one way and you want it to look a different way on an ongoing basis, the high level steps are:

Start writing the data in both formats
Convert the old format into the new format
Convert all read paths to the new format
Drop the old format

I'll be focusing on "Convert the old format into the new format". Create a new column in your table (I'll call it Polygon).

alter table [dbo].[GeoPolygons] add
   Polygon geography null;

Note that this is a prerequisite for the "Start writing the data in both formats" phase and so should already be done by the time you're ready to convert data.

The most straightforward method to do that looks like this:

update [dbo].[GeoPolygons]
set [Polygon] = geography::STGeomFromText(
    [Geometry].STAsText(),
    [Geometry].STSrid
)
where [Geometry] is not null
    and [Polygon] is null;

I'm making the assumption that the SRID on your Geometry column is set properly. If not, you'll have to find the SRID that is appropriate given the WKT that was used to create t

Points inside polygons - SQL Server T-SQL + geo

1 Answers1