Questions tagged [st-dwithin]
A PostGIS function to determine if geometries are within a specified distance of one another.
46
questions
2
votes
0
answers
33
views
PostGIS 3.3, ST_DWithin Query - "lwgeom_distance_spheroid returned negative!" by changing third arg? Postgres 11, Ubuntu 20.04
The unexpected error:
lwgeom_distance_spheroid returned negative!
By making a tiny change in the third argument (distance) to ST_DWithin, we get an error in one version but not another. In other words,...
- 21
1
vote
1
answer
82
views
Sorting rows based on calculated values on PostgreSQL's ST_DWithin
I have a question on how to improve sorting performance on PostgreSQL on geospatial data. I want to sort restaurants based on their distance and price, but it's noticeably slower than my study on ...
3
votes
1
answer
215
views
PostgreSQL not taking full advantage of all computing resources
Basically I have one table buildings_by_cell_300m, About 2490000 rows. I want to statistic all the geom information within 50 meters of each geom.
I am doing:
CREATE TABLE gbmi.bn_by_cell_300m AS (
...
- 45
1
vote
0
answers
61
views
Using geometry instead of geography
I have a basic query to run with PostGIS: research all LINESTRINGs close to my centre within a radius. "Linestring" is the only geometry used and represented by two points with longitude and ...
- 123
0
votes
0
answers
45
views
Big query execution time variations on a geographic query
I have a pretty simple query that I run on a table with a few million rows :
SELECT hp.id
FROM health_professional hp
WHERE hp.active = true AND (
(
...
2
votes
0
answers
94
views
Storing encoded polyline using PostGIS
I'm entirely new to PostGIS and trying to store encoded_polylines to test some proximity searches with lat/long points. So, I created a simple table composed of a pkid, an encoded polyline, and a ...
- 121
0
votes
0
answers
64
views
ST_DWithin with variable distance in PostGIS
I have a table of two columns:
Polylines (Linestrings) geometry coordinates.
Buffers: distance in kilometers.
The table contains about 1000 entries.
Using PostGIS "ST_DWithin" or any ...
5
votes
2
answers
140
views
Calculating cost values in pgRouting using ST_DWithin
I have a table where there are two geometries: geom_way (for routing roads) and (osm_meta) which is a triangulation from polygons. I use ST_DWithin function (between both geometries) with a distance ...
- 91
1
vote
0
answers
70
views
Unexpected ST_DWithin result when using geography [closed]
This query:
SELECT ST_DWithin(
ST_Buffer('SRID=4326;LINESTRING(7.1670005 45.9968257,7.1670216 45.9969537,7.1670216 45.9971326, 7.1670484 45.9972789, 7.1671905 45.9974074, 7.1672844 45.9974801, 7....
- 111
0
votes
1
answer
238
views
Why spatial filter (DWITHIN) is not working in WFS
I am working using spatial filter Dwithin for WFS. I have worked using a simple filter using to show no of earthquake data for the year 2020 in GeoJSON format from the given WFS:-
https://emidius.mi....
- 173
2
votes
2
answers
234
views
Finding nearest point of route using ST_DWITHIN
I'm new to PostGIS and distance comparisons and am having some problems with my current project. I have a an array of geopoints (long lat) which correspond to a route. And I need to find the nearest ...
- 23
4
votes
1
answer
353
views
ST_DWithin is returning all the data in the table
I am new to PostGIS and currently struggling with ST_DWithin function.
I have a table stores, which stores lat and long of locations in Denmark in the location column as geometry and with SRID 4326.
...
- 43
6
votes
3
answers
722
views
Invert the ST_DWithin() function
Is it possible to perform a query like this:
SELECT * FROM foo
JOIN bar
ON ST_Dwithin(foo.geom, bar.geom, > 10);
-- the > 10 means that I want to select everything that is ...
- 3,259
1
vote
1
answer
382
views
ST_LineInterpolatePoint Error - Does not exist
I am having an issue 'snapping' a points layer (be - bristol education) onto a road network (br - bristol_roads). I have created the code below using the PostGIS in Action guidebook as a reference ...
- 161
1
vote
1
answer
223
views
Optimising PostGIS ST_DWithin across many tables for existence only
I have 13 tables containing MultiPolygons in the same projection (27700). I am trying to write an efficient query to get back a Boolean for:
Is one polygon in table A within X meters of any polygon in ...
- 11
0
votes
0
answers
46
views
Different results for GEOGRAPHY and ST_TRANSFORM query on geo_point field
I am finding properties that are within 20K distance using ST_DWithin. But when I try
with ::GEOGRAPHY casting the result is different from ST_Transform. Note: SRID of geo_point field is 4326
Indexed ...
- 141
3
votes
1
answer
451
views
PostgreSQL + PostGIS count with ST_DWithin multiple distance at once
I am using ST_DWithin to count rows that are within 5KM, What I want is count on multiple distance at once with single query i.e count for 5KM, 10KM, 20M
Current Query
SELECT count(*) AS cnt FROM ...
- 141
0
votes
0
answers
27
views
Postgresql POSTGIS not using index field while having GIST index on geometry field [duplicate]
I have around 2M rows on my PostgreSQL table with GIST index on geo_point field but while I try to use ST_Distance or ST_DWithin it is not using the index instead it is doing Parallel Seq
Indexed ...
- 141
1
vote
1
answer
180
views
Find nearest polygons of a multi-line string
I am trying to find the nearerst (100-200 meters) parcels of the green line. Given The photo below, they database should return 12 records. I am using ST_DWithin postgis function. Is this the correct ...
- 328
2
votes
1
answer
610
views
ST_DWithin exponentially slow. Cannot find what I am doing wrong
PostGIS version: 3.1
PostgreSQL version: 12.3
The machine I am working with has: 126G RAM, 48 CPU cores
Info:
I am getting started with PostGIS.
My goal is to get all the matching data between two ...
- 23
3
votes
2
answers
205
views
Postgres: Remove Duplicate Rows returned by ST_Dwithin query
I am having trouble finding a query that returns one row for every unique vehicle crash that is within 100 feet of a select group of road features. My query currently returns a row for every collision ...
- 51
2
votes
1
answer
298
views
Extracting a Point from MuItipoint and use it for a ST_DWithin query
I have a MultiPoint geometry column which has only one point (DB is a live server which cannot be changed now).
I need to find all points within a certain distance from a fixed point I provide to the ...
1
vote
1
answer
482
views
Postgres ST_Distance_Sphere Runtime Improvements
I need to select all entries from a table, that are within a distance from a point.
Each entry is a point geometry (I set a gist index on geom).
Current runtime is anywhere between 3-15 seconds, ...
- 255
2
votes
1
answer
248
views
PostGIS - Find the "median" distance between two group of geometries depending on polygon area
I want to evaluate land distribution and concentration regarding to road network. Specificly, I'm trying to calculate the median distance between a shape of roads (multilinestring) and a shape of ...
- 23
2
votes
0
answers
94
views
How can I optimize this PostGIS query?
I am using this query in PostGIS to get the closests points around a trace. This query is taking a long time to run : 15 sec with only 800 records in the POIs table.
The first query get a list of all ...
- 141
1
vote
2
answers
603
views
Using table joins with ST_DWithin to optimise process?
I am trying to select all segments (among millions of them) that are less than X metres from a few thousands polygons obtained from a raster dataset (meaning they've got quite a complex geometry - ...
- 1,176
1
vote
1
answer
264
views
Ways to speed up ST_DWithin between millions of segments and thousands of polygons?
I have 3 PostgreSQL tables with the following data:
tab1 => It contains about 5.7 millions of segments (straight lines)
tab2 => It contains 6673 polygons
tab3 => It contains 19620 polygons
...
- 1,176
0
votes
1
answer
218
views
Improve Performance of ST_DWithin - H2GIS
I have a table seg_point, that I create like this:
CREATE TABLE seg_point (id INT AUTO_INCREMENT PRIMARY KEY, segmentid TEXT, geom GEOMETRY);
And I'm trying to improve the performance of this query ...
- 1
1
vote
1
answer
243
views
PostGIS: Running multiple intersections within a single SQL query?
PostgreSQL 11 + PostGIS, on Windows 10.
I'm working with a PostGIS-enabled database.
I want to UPDATE a large polygon table (100,000+ rows) WHERE the geometry of that host table intersects the ...
- 1,677
2
votes
1
answer
388
views
PostgreSQL: Spatial join points to polygon, listing point attributes where multiple are joined
I have a large polygon table and a large point table in a postgis enabled PostgreSQL database.
Each point has a unique reference number (double type). I want to spatial join the points to the polygons,...
- 1,677
2
votes
0
answers
798
views
Improve the speed for ST_DWithin
I need to get the outer polygon of the LSOAs within 10 miles of each postcode centroid. There are 180k LSOA polygons in table1 and 2.6 million postcode centroid points in table2.
I have created 2 ...
- 608
5
votes
1
answer
791
views
Using use_spheroid=false in ST_DWithin of PostGIS
I have created 2 geography columns and indexed them using GIST. I am looking to use ST_DWithin for a larger dataset and to make it faster trying to use the use_spheroid=false.
SELECT pois.objectid, ...
- 608
2
votes
0
answers
203
views
PostGis st_dwithin super slow when inserting
I am trying to insert the newly obtained data from st_dwithin into a new table.
However, it has been running since 1 day and I still can't get any data into the new table.
Can i know what is the ...
- 153
0
votes
2
answers
296
views
How to find all the geometries in a table which are at a particular distance from a given point in postgis?
I have a table called cartlocations:-
Now I have point geometry of latitude and longitude value, say 23.4877191,86.6927571, I want to find all the geometries which are within a distance of 1000 ...
6
votes
1
answer
413
views
ST_DWithin in PostGIS: Implicit index call
In PostGIS, the function ST_DWithin automatically includes a bounding box comparison that will make use of any indexes that are available on the geometries. This is done using the mechanism of ...
- 233
9
votes
1
answer
247
views
Finding closest line given 2 points using PostGIS?
I have a table t that contains a column line_positions which is of type line.
Given 2 points I want to find the closest line that is close enough (less than 10km) and that does not pass too close to a ...
- 191
4
votes
1
answer
2k
views
Postgis KNN vs ST_DWithin for nearest neighbour search with a radius
We are trying to get nearest neighbors within a user given radius. As far as I understand ST_DWithin degrades as distances are increased. The problem with using KNN is that it keeps on searching even ...
- 139
4
votes
1
answer
332
views
Postgis can't find the optimum way, seems to be always using Btree index rather than spatial index
I am using the Postgis ST_DWithin function like this:
SELECT *,
ST_X(geometry) as longitude,
ST_Y(geometry) as latitude,
ST_Distance_Sphere(
ST_SetSRID(
ST_MakePoint(-...
- 139
5
votes
1
answer
2k
views
Does the performance of Postgis function ST_DWithin degrade with larger distances?
I am using the Postgis ST_DWithin function like this:
SELECT *,
ST_X(geometry) as longitude,
ST_Y(geometry) as latitude,
ST_Distance_Sphere(
ST_SetSRID(
ST_MakePoint(-...
- 139
11
votes
1
answer
4k
views
ST_DWithin Calc Meters - transform or cast?
I want to use ST_DWithin on data which is stored in SRID geometry of 4326 and use meters as the distance parameter. Is it more efficient to do a cast (e.g. data.geom::geography) or a transform to an ...
- 183
1
vote
1
answer
763
views
ST_DWithin Optimization Postgis
I've got two datasets - 1) a set of buildings in LA and 2) fiber optic cable maps from a company called Zayo which covers the whole of the US. I'd like to find the buildings within a certain distance ...
- 183
5
votes
2
answers
4k
views
How can I optimize this ST_Distance query?
I am using ST_Distance in postgis to get the closest point in a table to each polygon in another table. This query is taking a long time to run (2 hours with 18,000 records in the polygon table and 7,...
- 1,396
2
votes
2
answers
889
views
How many query points fall within distance X to a set of LineString
I have a set of 10 query points and I want to know how many of them are with distance within at most 4 meters of any LineString in a given table.
Note that if a point is within 4 meter distance to ...
- 167
2
votes
1
answer
3k
views
Improve performance on a st_dwithin query (in PostGIS)
At the momenent I am working on a query, as described in an earlier question. I have the two tables
testme (tracked GPS-profile with Point Geometry) and
roads (geometry roads shapefile)
Besides ...
- 51
3
votes
1
answer
5k
views
How to use ST_DWithin to query features from one table within a distance of a specific feature or features in another table
I'm pretty new with PostGIS. I have a parcel layer and a census block layer in a postgres database, and I would like to be able to query out all census blocks within a specified distance of a specific ...
- 1,396
14
votes
2
answers
6k
views
ST_Distance doesn't use index for spatial query
I can't get PostGIS 2.1 running on PostgreSQL 9.3.5 to use a spatial index even for the simplest queries. The whole dataset is 8 million points (population count grid from here). The table is created ...
- 243