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
2answers
68 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
28 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
25 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
36 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
41 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
46 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
85 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
58 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
39 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
156 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
27 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
18 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
97 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
19 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
169 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
128 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
129 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
68 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
245 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 ...
3
votes
1answer
226 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
79 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
55 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
68 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
87 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
123 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
142 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
61 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
207 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
99 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
136 views
PostGIS: 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
295 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
230 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
277 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
159 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
287 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
298 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
92 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
125 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
581 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
224 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
130 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
659 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
101 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
363 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
313 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 ...
4
votes
2answers
400 views
ST_Intersection of 3 polygons
I am new to postgis and I need to find intersection of three polygons.
In my table named "mytable" contains large no of polygon coordinates and grouped into three categories by adding a field called "...
3
votes
0answers
42 views
Intersect a line considering the topological direction
I have a set of lines in postgis, and I want to intersects those lines which only go from point A to point B. And I don't want those lines which go from point B to point A. So, my question is : how I ...
4
votes
1answer
1k views
Postgis Clipping two postgis vector layer without changing attribute table
What I want to do is make the purple layer at the back having the same shape as the transparent layer.
However, I don't want any attributes of the transparent layer to be joined to the purple layer, ...
