On Tue, 23 Aug 2022 22:05:48 -0700 (PDT), Jason Williams wrote:
> I have a table cost_poly with only 10 thousand rows, each row is a
> MULTIPOLYGON. Then given a point (, ), find the polygon the point is
> in and return the distance:
>
> SELECT pk_uid, ST_Distance(src.srcpt, ST_Boundary(geometry) as
> nearestdist
> FROM coast_poly, (SELECT ST_Transform(MakePoint(, , 4326), 26917) as
> srcpt) as src
> WHERE coast_poly.ROWID IN
> (SELECT ROWID FROM SpatialIndex WHERE
> (f_table_name = 'DB=main.coast_poly') AND
> (search_frame = src.srcpt))
> AND
> ST_Intersects(src.srcpt, geometry)
>
> Out of the 10k rows, only 200 rows have ST_NPoints(geometry) > 516
>
Hi Jason,
your SQL query seems to be correct, I'm unable to see any defect.
I've tested it on a dataset with more than 75,000 multipolygons
(about 1,700 of them having ST_NPoints() > 512, the maximum
value being 2,500), and I've measured a mean response time
of about 250 milliseconds.
it definitely seems what I call a "fast running query";
what do you exactly intend when you say "arcgis seems a
little bit faster than ST_Distance" ?
less than 250 millis ?
On Tue, 23 Aug 2022 22:05:48 -0700 (PDT), Jason Williams wrote:
> Will ST_Subdivide help?
>
I strongly doubt.
a prelimiary call to ElementaryGeometries() so to resolve
all multipolygons into elementary individual polygons
could be a more effective solution, because in some peculiar
cases it could allow the Spatial Index to work more selectively.
bye Sandro