5
votes
Trying to get accurate results using ST_DWithin with srid -1
tl;dr:
Given your setup, the most optimized, accurate query would be
SELECT
*
FROM
location
ORDER BY
ST_SetSRID(location.point, 4326)::GEOGRAPHY <-> ST_SetSRID(input_point, 4326)::...
- 29.3k
4
votes
Intersecting Node A and Node B columns for lines table
Judging from the table presented as "OUTPUT from QGIS", the result should be unique on column "ID" which, when compared to the "OUTPUT" above, is actually column b."...
3
votes
How to intersect with ST_Buffer in PostGIS
If you don't specify column names in the CTE, they will have the same names as in the inner select statement, so here the column would be named st_buffer instead of geom.
You can set the name at the ...
- 40.8k
3
votes
Find all geometries in one table that do not exist in another
The plain = implementation for the GEOMETRY/GEOGRAPHY types directly compare their encoded byte sequences, which naturally is much faster than determining geometric likeliness, especially for higher ...
- 29.3k
3
votes
_core.QgsProcessingException: Unable to execute algorithm Incorrect parameter value for TARGET_LAYER
I solved my own issue passing target_layer as an URI layer doing:
# Define the connection parameters using QgsDataSourceUri
uri = QgsDataSourceUri()
uri.setConnection(host, port, dbname, user, ...
- 51
3
votes
Accepted
QGIS & PostGIS STRING_AGG in virtual layer definition returns "no such function: STRING_AGG"
Virtual layers don't use the underlying storage provider(s) but rather sqlite/spatialite, so postgres specific functions are not available.
You can instead create a query layer from the DB Manager, or ...
- 40.8k
2
votes
PostgreSQL/PostGIS database in OneDrive?
I do use a PostgreSQL/PostGIS database in an OneDrive-Cloud and access it with QGIS. I created a folder pg_data within the local OneDrive and specified it as the data storage destination along the ...
- 3,180
2
votes
Accepted
How to call newly made variable
There are many possible, more or less elegant and performant solutions in SQL - this one attempts to stay within basic JOIN set theory.
Theoretically you would create a FULL OUTER JOIN result set and ...
- 29.3k
1
vote
Use ST_Distance() to filter a maximum of 1000m
You had better to use CTE (Common Table Expression) to make your query readable. If you have spatial index on geom column then the ST_DWITHIN function is more effective. I suppose you forget to put ...
- 7,235
1
vote
Accepted
Determine area enclosed by multiple segments
Solution for PostgreSQL/PostGIS.
Input geodata table named "linetoarea" shown in Figure 1 below.
Figure 1
Run Geo-SQL script:
WITH
noded AS (SELECT ST_Node(ST_Union(geom)) geom FROM ...
- 4,302
1
vote
Intersecting Node A and Node B columns for lines table
Do you just want to get start and end node information for your LineStrings?
I.e. something like
SELECT
oc.*,
sn."ID" AS start_node,
en."ID" AS end_node
FROM
pia.or_chamber ...
- 29.3k
Only top scored, non community-wiki answers of a minimum length are eligible