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
40 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
16 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
79 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
77 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
38 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
41 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
72 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
62 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
143 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
69 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
45 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
158 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
34 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
22 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 ...
4
votes
1answer
117 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
30 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
226 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
31 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
170 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
182 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
76 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
1answer
338 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
280 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
92 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
82 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
73 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
93 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
145 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
152 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
65 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
227 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
106 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
148 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
334 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
263 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
296 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
170 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
349 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
0answers
330 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
98 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
140 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
651 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 ...
1
vote
2answers
260 views
Use area of intersection as a variable in PostGIS
I have two geojson files - NYCSample and censusTracts. Each census tract contains a population attribute (integer). My objective is to multiply the population attribute by the % area of intersection ...
1
vote
1answer
141 views
Subtract MultiPolygon table from LineString table
I've two tables, one with a roadnetwork and another one with some polygon. The roadnetwork table has a geometry column defined as
COLUMN geom geometry(LineString,4326);
while the polygon table has a ...
2
votes
1answer
750 views
Change ST_Intersects default tolerance [duplicate]
The default tolerance of PostGIS ST_Intersects query is "0.00001 meters" as indicated in the doc. I need to use the same query with another tolerance value but ST_Intersects takes only 2 input ...
1
vote
1answer
109 views
POSTGIS error in getting proper intersected output (St_intersect, st_buffer)
I need to get gid, area and geometry of table1 which are intersecting with
200 buffer of table2. I tried to structure the query. Please refer below:
create table SMI_1 as
SELECT ST_Buffer(geom,...
2
votes
1answer
385 views
PostGIS slow intersects query
I'm having trouble optimizing this query, sometimes it will take around 10s to execute. I have indexed the columns samples.geom and jbb2016.geom_3021 so that shouldn't be a problem.
I know this is ...
2
votes
1answer
345 views
How to retrieve Intersected geometry of the polygons geometry POINT (lat,lng) input?
I am passing a particular state (Indian states) latitude/longitude to the spatial query where it has to check intersection with the other table shapefiles polygon geometry. If that polygon is within ...
