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.
1
vote
1answer
41 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
29 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
79 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
71 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
52 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
39 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
54 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
28 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 ...
4
votes
2answers
103 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
82 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
48 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
27 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
45 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
125 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
100 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
226 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 ...
5
votes
1answer
73 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
47 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
159 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
41 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 ...
0
votes
0answers
24 views
Intersecting files with different projections
I am attempting to calculate the intersection of two files(vectors/shapefiles) that have different projections but would in real life have actual overlap. I am attempting to do this using the ogr ...
5
votes
1answer
142 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
0answers
31 views
How to use our polygon intersect on all rows on 2 dataframes
If I have to intersect all rows of one dataframe geom column with another dataframe geom column without using for loop, how can I do that?
geom1 = ogr.CreateGeometryFromWkt(df['geom_value'][0])
...
0
votes
2answers
292 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
22 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
34 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
195 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
229 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
81 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
440 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
337 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
101 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
98 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
77 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
96 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 ...
0
votes
0answers
156 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
165 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
68 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
252 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
115 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
165 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
380 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 ...
1
vote
1answer
295 views
ST_Intersects optimization between lines and points
I'm currently trying to optimize my code and I wanted to know if someone could enlighten me concerning the use of ST_Intersects.
Basically, I'm trying to assign some points to each road sections ...
3
votes
2answers
314 views
Postgis st_intersects not selecting correct features [closed]
I am running a very simple boolean selection with postgis st_intersects on two polygon tables:
select distinct on(ms.shape) ms.shape shape,ms.type_
from riparian_corridor.merged_soils ms join ...
3
votes
2answers
187 views
How to intersect Box3Ds with PostGIS 2.3?
I am storing several axis aligned bounding box representing complex gemoetries as
BOX3D(POINTZ(x1,y1,z1), POINTZ(x2,y2,z2))
I need to perform queries to identify elements that intersects a given ...
1
vote
2answers
391 views
Get Clipped geometry by a given bbox in postgis
I tried to get the geometry inside a bbox from postgis table, and I follow this instruction:
And I use the following sql ( the ? will be replaced by the bbox):
SELECT name, ST_Intersection(geom,...
1
vote
1answer
376 views
ST_Intersects with subquery in Spatialite
I am having issues with what seems to be a very simple spatial query with a sub-query in Spatialite. I am trying to find all the buildings (poly) that intersect with a service area (poly), but only ...
3
votes
1answer
107 views
Selecting points having 1, 3 or more lines intersecting using PostGIS
Here's the problem : I have two tables, one with roads, the other with points (which are startpoints and endpoints of each road sections, without duplicates).
I want to extract only points that have ...
0
votes
1answer
144 views
Recursive st_intersects with self query
I'm trying to find the polygons a point is in and then the intersecting polygons of the result of the first query.
I'm trying along the lines of:
WITH RECURSIVE all_the_areas AS (
SELECT a.name,...
6
votes
1answer
734 views
postgis query finding MultiPolygons within lat/lng bounds
Question Summary
I am trying to find all the MultiPolygons within lat/lng bounds, and I am unable to get a valid query. Ultimately I believe I have not transformed the MultiPolygons into something i ...
