Efficiently creating and querying complex polygons

Topics: Developer Forum
Dec 9, 2009 at 2:43 AM

Hi,

I've been investigating using MsSqlSpatial to store large numbers of polygons (series of longitudes/latitudes) and then locating which of these polygons a particular point lies. I've managed to get things working nicely in simple cases, but as the number of points in the polygon grows, the polygon construction quickly slows to a halt.

I am constructing polygons using something like:

ST.PolygonFromText('POLYGON((' + @points + '))',4369) where points is a string constucted from longs and lats stored in some table. With sufficiently large point datasets, this dies.

To find if a point lies within this I'm using:

SELECT [ST].[Contains] (ST.PolygonFromText('POLYGON((' + @points + '))',4369), ST.PointFromText('POINT(long,lat)',4369))

Again, works just fine until we hit a large dataset, where the polygon isn't even created.

My question: Are there more efficient ways to build up polygons for querying?

Any advice is much appreciated!

Developer
Dec 9, 2009 at 12:02 PM

Hi garibaldi, it sounds like you are just trying to query with arbitary data not stored in the database? In which case NetTopologySuite (used internally by mssqlspatial) may be a better bet.. The chances are you are suffering from IO bottlenecks actually getting the data to the database..

Alternatively you could try sending WKB which is far less verbose or actually store the geometries in the db (where they are stored as wkb with and extra dword for srid)

hth jd