I am new to PostGIS. I have a set of (thousands) of Linestrings and associated multiple points along the line. I want to divide each line into a set of line segments consisting of only 2 points. I find a few answers, But none of the answers I want. for example https://gis.stackexchange.com/questions/21648/exp…
Tag: postgis
Handle pg_error on generated columns
I have a table that consists of some PostGIS related data. These data are generated automatically on INSERT or UPDATE. Sometimes the data provided on the polygon column might not fit the generation function and cause an error. I wanted to handle this error and set a default value when it fail. — Last re…
Correct use of a LATERAL FROM
I am trying to use PostGIS to undertake a number of steps within an SQL statement to basically convert a raster to points, buffer, dissolve and then determine the overall boundary i.e. concave hull based on an attribute. The following is where I am at but get an error suggesting the use of a LATERAL FROM but …
How to insert value to a column as a result of geometry of another column in the same table
i have the database table posted below. the two columns geometryOfCellRepresentativeToTreatment and geometryOfCellRepresentativeToBuffer are of type geometry. and their value is equal the the geometry of the column fourCornersRepresentativeToTreatmentAsGeoJSON_ and fourCornersRepresentativeToBufferAsGeoJSON r…
how to split multilinestring into overlapping 2-point subsets (2-point linestrings)?
I want to split a multilinestring or linestring like LINESTRING(0 0, 1 0, 1 1, 0 1) into linestrings consisting of two points. So the linestring above would become: LINESTRING(0 0, 1 0) LINESTRING(1 0, 1 1) LINESTRING(1 1, 0 1) I would also like to be able to do the same with multilinestrings. Answer Use a LA…
how to iterate through geojson elements
i want to execute the code in this question https://gis.stackexchange.com/questions/142391/storing-geojson-featurecollection-to-postgresql-with-postgis/142479#142479 but when i run the app i receive the following error: please let me know how to fix it. code: attempts: Answer From the database perspective the…
Using Triggers in PGAdmin4 v5 to get the geom of a line being generated in QGIS
So I have a database of pipes that is visually coded in qgis. My goal is to add a trigger to PGAdmin4 v5 that uses ST_StartPoint() and ST_EndPoint() to get the end points of the polyline (the pipe) as it is being entered. Then, I was going use this geometry to grab the nearest structure (a point) and autocomp…
PostGIS: Transform Linestring to LineString ZM
Problem: I have a series of geometries (LineString) saved in a Postgres database with additional information saved next to the geometry (a column for speed and time). I want to combine the three columns into a LineString ZM type so the geometry goes from a 2d to a 4d vector. id geometry speed time 1 LineStrin…
Inserting large amounts of generated test data into postgresql database
I want to insert a billion rows of floaty data into a postgresql database so that I can test the performance of various postgis functions. My effort below takes ages and seems to be really inefficient …
PostgreSQL query update returns UPDATE 0
I am having trouble with a PostgreSQL query where my update condition contains more prerequisites. I attached 2 images to describe my problem. I want to update the highlighted row But the response is: Answer Your problem might be somewhere else (see this db<>fiddle) A few thoughts on your data: Reduce t…