Questions tagged [st-intersects]
ST_Intersects is a SQL function that takes two geometries and returns true if any part of those geometries is shared between them.
80
questions
3
votes
2answers
75 views
Combining ST Buffer and Intersect with Where clause
I'm trying to create a buffer around a single point in a spatial entity and finding all the points from a different entity that fall within that buffer.
I tried two approaches. The code below runs ...
5
votes
4answers
224 views
Finding truly intersected polygons
Intro
Short version: My goal is to find geometries intersected by a target polygon.
To find overlaps - the most straightforward choice is to use ST_Overlaps or ST_Intersects. But this method will ...
2
votes
1answer
53 views
Why do two tables with valid geometry return self-intersection errors when intersected?
I have two tables A and B, both have valid geometries when I check with ST_IsValid. In my query, I intersect the two and the query stops in the middle because of this error:
ERROR: ...
1
vote
0answers
86 views
Speed up PostGIS raster query
I have a simple PostGIS table as follows:
\d rad;
Table "public.rad"
Column | Type | Collation | Nullable | Default ...
2
votes
1answer
75 views
Optimising Large Spatial Queries in SQL
I'm sure similar answers have been given elsewhere but no matter what I have tried I am unable to optimise this query.
Details: TableA has 3.5mill records TableB Had 57000 records but I have ...
0
votes
0answers
35 views
PostGIS - Tables UNION
I have three tables (Table_1, Table_2, Table_3) with the same fields:
geom, Field_a, Field_b, Field_c, Field_d.
I want to SELECT these tables so that all their rows appear but I want do not want rows ...
1
vote
1answer
26 views
Fails ST_intersects(geom, select statement) for drive time
I am trying to select houses in a drive time isochrone.
I have the code working fine to make the isochrone.
I can't place the results in a seperate table
I need the isochrone in a where clause
Is ...
2
votes
1answer
155 views
ST_Intersection + ST_Difference + not overlapping Polygons
I have a PostGIS table with some overlapping polygon and need a PostGIS view where no polygon is overlapping and the data is aggregated (like in the picture).
With a Union of ST_Intersection() and ...
1
vote
0answers
60 views
Optimize a query with st_intersect()
I have three tables containing increasingly-complex geometry. I'm clipping Tables 2 and 3 to the first, least-complex table (with 700 multipolygons). The other two tables contain spatial attributes I'...
2
votes
0answers
58 views
Behaviour ST_Intersection of 3D LINESTRING and 3D POLYGON - Z value with GEOS backend
I do not quite understand the behaviour of ST_Intersection applied to 3D geomerties when using the GEOS backend. Take this example:
SELECT ST_AsText(ST_Intersection('POLYGON((1 1 1, 6 1 3, 6 4 3, 1 4 ...
1
vote
1answer
62 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 ...
2
votes
1answer
272 views
Getting “missing FROM-clause” error when adding ST_Intersects constraint to PostGIS table
I have two tables in PostGIS: afrivs, which is a polyline of various rivers, and weirs2 which is an empty polyline layer where potential dam sites will be added.
Obviously dams have to cross a river ...
1
vote
0answers
106 views
Uncompressed vs Subdivided: which one offers better PostGIS INTERSECTS performance?
In my PostGIS I have a point table (roughly 51 millions rows) and a multipolygon table. I need to find a polygon label/ID for every single point in my point table (roughly 51 millions rows) in PostGIS....
2
votes
2answers
189 views
Spatial joining two line layers and checking intersection in PostgreSQL/PostGIS
I have two line layers in PostgreSQL/PostGIS. I want to the select data which are not intersecting. When I am writing this query it is taking a lot of time. How can I make it faster and see the result?...
2
votes
1answer
80 views
Find a point in a multipolygon geometry
I need to know which points from Table 1 are contained in Table 2.
In Table 1 I have longitude and latitude and I made points with ST_SetSRID(ST_MakePoint(d.longitude, d.latitude),4326) as geom
...
1
vote
2answers
59 views
write z-value from polygon layer to point layer in QGIS
I have two layers:
1 polygon layer where the depth of an area is written in the database (as DRVAL1) and and where the depth is exported as a Z-value to the geometry.
1 point layer that I use to plan ...
1
vote
1answer
71 views
Efficiently query two properties QGIS spatialite
I have a simple query that involves two steps:
non-spatial criteria (should be super-fast)
spatial criteria (which is really slow)
I would like to run the spatial query only on the filtered layer (...
0
votes
1answer
330 views
MSSQL performance with Geography (point within polygon)
I have a query, below, that works - however its very slow, looping through roughly 24,000 points per second. This is a rather high end production DB server with MSSQL and SDE, and in other instances a ...
3
votes
2answers
264 views
Find what polygons are not fully covered by union of polygons from another layer in PostGIS
I have two tables in a PostGIS database
>>> select postgis_version();
"2.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1"
One represents the country borders and another one is administrative ...
2
votes
2answers
101 views
PostGIS intersects and case function
Hi i've been setting up a script to assign values depending on one type of polygon intersecting with another polygon. I have two feature tables - one with building footprints and one with polygons ...
1
vote
1answer
97 views
ST_Intersects one polygon with layer - always returns 0 results
I'm trying to write a query to find if any features in a layer intersect with a certain polygon, but it is always returning 0 rows. I've tried messing with SRIDs, different geom types, sizes, shapes, ...
0
votes
1answer
31 views
Intersect lines and polygons [closed]
I am tring to intersect lines with a polygon grid, but i got an error when i run this:
create table "st11_01_vd" as
SELECT (
"grid_1km.gid",
"st11_012017_ln.linetime",
ST_INTERSECTION("...
1
vote
1answer
102 views
PostGIS ST_Intersects result is ignored by where condition
I have a simple query filtering by st_intersects function finding all lines intersecting with a polygon.
select *, st_intersect(graph_line, st_geomfromtext('POLYGON((261 156,148 452,333 507,566 199,...
1
vote
1answer
349 views
Efficient intersect query for virtual layers in QGIS
I have 3 tables/shapes as follows:
ADRR (adress points)
ADRR_ID | ST_ID (street surface id) | point geometry
STREET
ST_ID | ST_NAME (street name) | polygon geometry
Obstacles
ID_OBS | ...
0
votes
1answer
219 views
Subquery inside ST_Intersects
I have 3 geometry tables a, b and c with geometry column geom
What I want is to first check if geom of b and c, intersects under some given condition using a subquery, and feed the subquery geometry ...
2
votes
1answer
414 views
Slow PostGIS line intersection even with indexes
I have line data contained in different graphs. Querying data over multiple graphs and using st_intersects results in slow queries. All lines are represented as geometry(LineString).
I am querying ...
4
votes
1answer
92 views
Postgis spatial index not being used
I have a query to calculate how many crimes (of a certain type) are rural and urban:
select st_intersects(c.wkb_geometry,u.wkb_geometry) as ur,count(c.*)
from crime.street_crime c, meridian.urban ...
2
votes
0answers
55 views
postgis and st_intersects ; fail to compare a table to itself with a left join
postgresql 9.6, postgis 2.3
Following this Select features that do NOT intersect in PostGIS, I would like to compare one table to itself, to keep all records from table p1, even and above all those ...
5
votes
2answers
188 views
Postgres/PostGIS skip intersect on features already intersected
I have a collection of polygons(building outlines called "Build_poly") that I am intersecting with another table of flood defenses (also polygons called Flood_poly).
When ever one of the building ...
0
votes
0answers
98 views
OGR SQL Intersect layer slow to iterate
I am having an issue with OGR's ExecuteSQL, but not with the actual SQL query.
What I am intending to do is to take a large number of polygons ~500k and find out if they intersect with a ...
5
votes
1answer
202 views
SQL / PostGIS: Expanding polygons contained inside another polygon until one vertice touches bounding box
We have a problem that we are not quite sure how to approach, we have a number of polygons contained inside a larger bounding polygon, We need to expand these internal polygons as much as possible but ...
0
votes
2answers
446 views
ST_Intersection not returning all intersecting geometries
I have a query that will calculate the proportions of given columns based on the area of intersection. The returns mostly the polygons entirely inside the other polygon, and in some cases returns the ...
0
votes
0answers
27 views
MultiPolygon Intersect only working for first Polygon in collection, how can I fix this?
I am very new to PostGIS.
I have a table called regions, that has name, an id and a geojson that is a MultiPolygon.
Now I need to check whether certain points or other MultiPolygon regions intersect ...
1
vote
1answer
51 views
ST_Overlaps and related
I have a table with ~15K records consisting in points with a 200m buffer and I need to investigate how many of them are in proximity of polygons contained in another table (~20K).
I wrote a function (...
0
votes
1answer
289 views
Removing duplicate rows created by ST_Intersects in PostGIS?
I want to basically take one spatial table of polygons and determine if any of the polygons interest with another table of polygons and save the results in a new table with a new column saving the ...
1
vote
2answers
354 views
Creating View using spatial queries in PostGIS?
I am trying to create a view from a selection. One layer contains indian states boundary as polygon and other world forest fire as points.
CREATE VIEW myview AS
SELECT *
FROM ...
2
votes
0answers
118 views
Creating a trigger containing St_intesects in Postgres
I am new to SQL. I need a help creating a simple trigger function that can contain records from a intersection.
I have points layer(vnp14imgtdl_nrt_global_24h) and a polygon boundary(states).
The ...
2
votes
2answers
1k views
PostGIS ERROR: Relate Operation called with a LWGEOMCOLLECTION type
I have installed PostGIS on my machine and I am using shapefiles provided by the US Census Bureau for finding zips and counties of coordinates. I am just using the PostGIS provided method for loading ...
4
votes
1answer
516 views
r - Can dplyr::group_by work with sf::st_intersects
Trying to combine dplyr::group_by and sf::st_intersects to see which of the objects within groups in a sf dataframe are intersecting with each other but in the end all objects are compared with all ...
0
votes
1answer
161 views
Slow spatial join on many tables
I try to create a view in an Imposm database for rendering purpose.
This view is merely a copy of the osm_resorts table, but with another column
members_types that concatenate all the type strings ...
1
vote
0answers
176 views
Intersecting two geodatabases with ogr
I am trying to create an intersection between two different layers of two different geodatabases using ogr2ogr, but without success.
I have been using the following command line prompt:
ogr2ogr.exe ...
0
votes
1answer
85 views
Postgis st_intersects unexpected results
Trying to update one table of parcels with st_intersects from another.
Results always update all table records and don't discriminate using st_intersects.
Aim of query is to update road_class value ...
2
votes
0answers
108 views
Hibernate spatial intersects() over antimeridian
I have a DB full of objects that contain a location (usually a polygon). I have a query defined that uses Hibernate Spatial's intersects() method to find all the objects that are with a box that's ...
1
vote
0answers
215 views
3d Intersect query compatible with GeoTIFF/DEM?
When researching this topic I haven't been able to verify if it's possible to make spatial 3D Intersect queries between a vector polygon with z-values and GeoTIFF/DEM file holding the elevation values?...
1
vote
1answer
201 views
postgis st_intersection of linestrings geometric miscalculations
Below, is a PostGIS SQL query where I have a table called nullss (polygon and line geometry) and a table called zones(polygon and line geometry as well) where the zones table spatially surrounds the ...
0
votes
1answer
87 views
Q: Issue finding the nearest street segment(s) based on an x,y coordinate and ordering by distance
I'm trying to pull the SNOW_ROUTE attribute from the nearest street segments based on an X,Y coordinate and order the results by nearest segment. I'm able to return the snow route information but the ...
1
vote
1answer
352 views
Error in PostGIS intersection
I have a PostGIS query that counts the length of individual lines in each buffer:
SELECT
buffered.name,
ST_LENGTH(ST_Intersection(buffered.buffer, lines.wkb_geometry)) AS total_length
FROM ...
1
vote
1answer
144 views
st_intersects of zero area polygon and point returns false
I have the following three geometries:
PT: Point(0 0)
LINE: LineString(0 0, 0 10)
POLY: Polygon((0 0, 0 0, 0 0, 0 0))
Calling st_intersects on LINE and POLY returns true (as though POLY describes ...
3
votes
1answer
211 views
Find shared border
I have two polygons (green and red in the image) and want to find the shared border using PostGIS. If I use select st_intersection(green_geometry, red_geometry), PostGIS sometimes returns a point, not ...
2
votes
1answer
469 views
Group intersecting/overlapping com.vividsolutions.jts.geom.Polygon
I have list of geometry type in a database or:
List<com.vividsolutions.jts.geom.Polygon> polygons
Is there any JTS Topology Suite method or PostGIS function which would output intersecting ...
