3
You would need to group by ID. Then you need to compute the distance and keep the biggest one.
Note that st_maxDistance is usefull to know the distance between a point and the furthest part of a polygon
CREATE TABLE public.vertexmaxdistances AS
SELECT a.id, max(ST_Distance(a.geom, b.geom)) as dist
FROM public.insidepointtable a, public.vertextable b
WHERE ...
3
This:
UPDATE coordinates
SET geom = ST_Transform(
ST_SetSRID(
ST_MakePoint(longitude, latitude),
4326
),
3857
)
is transforming your lat-long coordinates in the two columns into a geometry in SRID 3857, which is not degrees lat-long - its a cartesian system used by Google ...
3
The update query is taking forever because you have introduce a cross join between the point table and a copy of it (UPDATE table1 SET ... FROM table1 AS t1). We can see that in the explain plan with the number of rows to be updated (rows=15 380 552 946 848)
When doing an update, each row is processed individually so you don'
t need the lateral join. You ...
3
The point is, if you GROUP BY the row id column, you will get one result row per input row (this is equal to grouping by the actual date column)! And since a Linestring is only valid with a minimum of two points, ST_MakeLine adds the same point twice.
Either run
SELECT ST_MakeLine(geom ORDER BY date) AS geom
FROM observations
;
to get one line for all ...
2
It could be a couple of things:
Most likely, Postgres is not able to dynamically route (pull-down) spatial
conditions from top-level join to individual tables behind unioned
view.
It also could be caused by your CTE clause. CTE acts as an
optimization fence. In this case, it should not be an issue, but with
combination with join and unioned view, who knows....
2
To fill in with the mentioned ST_MaxDistance, assuming your <polygontable> has an <id> that matches insidepointtable.id:
CREATE TABLE public.vertexmaxdistance AS
SELECT a.id,
ST_MaxDistance(a.geom, b.geom) AS dist
FROM public.insidepointtable AS a
JOIN <schema>.<polygontable> AS b
ON a.id = b.<id>
;
...
1
You should definitely try to subdivide your boundary polygons! The spatial index serves only to select candidate geometries based on their bounding boxes. Then real geometry has to be rechecked for each candidate to produce real results (as you can see in the execution plan). So... if your boundary polygon is complex, that recheck is very expensive! That can ...
1
PostGIS needs to be compiled with protobuf support. If not compiled with protobuf support, you will get error message: error: Missing libprotobuf-c
Steps to compile PostGIS with protobuf support:
#install from repository (if possible)
apt-get install protobuf-c-compiler
#install protobuf (only if protobuf-c-compiler from repository is
#not working or ...
Only top voted, non community-wiki answers of a minimum length are eligible
