using Spatialite as alternative to arcgis for distance to coast lookup

[Jason Williams]

Anyone tried using Spatialite to query distance to coast lookup? 

I tried use ST_Distance function from spatialite, the distance query returned pretty good data that is matching the result from arcgis query. However I could not make the speed to match arcgis. arcgis seems a little bit faster than ST_Distance.

Is there a way faster than ST_Distance?

[Peter Johnson]

What type of query are you performing?

Is from point to point(s), or point to polygon(s)?

Are you querying one-to-one or one against a table of candidate matches?

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/48f9fa52-965a-4bac-b182-48c4a59c9192n%40googlegroups.com.

[Jason Williams]

I have a table cost_poly with only 10 thousand rows, each row is a MULTIPOLYGON. Then given a point (<lon>, <lat>), 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(<lon>, <lat>, 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 

Will ST_Subdivide help? Can spatialite divide the geometry and store the subdivided geometry into a new table?

Thank you !

[Jason Williams]

Correction: table name is  coast_poly

[Jason Williams]

 coast_poly table is imported from shape file.

[a.fu...@lqt.it]

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

[Jason Williams]

Thank you Sandro! The difference is actually negligible as I measured more times.