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.
115
questions
5
votes
1answer
107 views
Counting geometries which intersect another geometry using PostGIS
I'm trying to calculate the total area for each layer I have within a specific area and count the geometries which intersects with that specific area.
I can calculate the area and the results seem ...
4
votes
1answer
161 views
Intersect polygons returns true instead of geometry
I try to do a simple cut between two layers and PostGIS returns a boolean instead of a geometry
select
t1.id,
st_intersects(t1.geom, t2.geom) geom
from
public.table1 t1
inner join
...
0
votes
0answers
20 views
STBuffer is returning wrong values
I am trying to get nearest point from the given coordinates within 1km buffer in SQL.
Below is the code which I am using to get nearest point
DECLARE @g geometry;
SET @g = geometry::STGeomFromText('...
0
votes
1answer
73 views
PostGIS ST_Intersect - dealing with different coordinate systems
I try to get an intersection of two tables - lines and polygons. Lines' CRS is 3006 (SWEREF99 TM), while polygons' CRS is 4326 (WGS84). I write the query:
SELECT v.subid
FROM line AS v, polygon AS o
...
5
votes
1answer
71 views
Applying ST_Intersects in a query with PostGIS
I have a PostgreSQL/PostGIS materialized view (study_region_all) with stream lines and a table of county boundary polygons (counties). They both have a column called co_fips but for a subset of items ...
0
votes
0answers
54 views
Super-efficient geo-intersection operations in Python
I am working with big spatial data. I have about 2.3 million multilinestrings (driven routes) and I seek to count the number of intersections of each individual multilinestring with a set of ~60-150 ...
4
votes
1answer
114 views
ST_Intersects with the whole world doesn't bring back all results
I have a table of devices with a column last_known_coordinates (geography(Point,4326)).
I want to find devices within a bounding box with a query. Some bounding boxes are working, but others are not - ...
3
votes
0answers
39 views
PostGIS ST_Intersects giving unexpected results
I have a table of devices with a column last_known_coordinates (geography(Point,4326)). Here is an example last_known_coordinates:
0101000020E6100000569C00000D99EDBFE006C60B33F54640
Which is in ...
6
votes
2answers
187 views
PostGIS ST_Contains / ST_Intersect with one point envelope
With PostGIS I have to test wether a point is inside an envelope or not. Sometimes the envelope is constructed from a point and a few others next to it, and sometimes only from a single point, against ...
3
votes
0answers
77 views
PostGIS Query using ST_Intersects not using spatial index
As code is better than 1000 words, here we go :
explain analyze SELECT DISTINCT(zones.office_id) AS office_id FROM "zones" JOIN offices ON zones.office_id = offices.id WHERE ST_Intersects('...
0
votes
0answers
26 views
PostgreSQL Trigger to Change geometry of Point on Different Polygon
Issue :
I have table in Schema GIS
1. Asset ( Id, User, Begin Date, Status, Master , Geometry)
2. Room ( Id, User, Begin Date, Status, Master , Geometry)
And I have table in Schema Public
1. Asset ( ...
1
vote
0answers
21 views
Calculating costs of a line geometry based on a different intersecting geometry
I have 2 tables, a route table with line geometry and a duct table also with line geometry. Basically I need to calculate the costs of the different routes based on their intersection with the duct ...
1
vote
0answers
34 views
How to count points intersecting with the end of a multilinestring?
I need to count points from table1 that intersect with the lines in table2 which has about an equal split between linestrings and multilinestrings.
Intersect works fine, but the thing is that I need ...
1
vote
1answer
1k views
R: Using st_intersects to classify points inside, outside and within a buffer
I have two objects, a shapefile of location points, points, and a shapefile of polygons, world_buffer (both +proj=utm +datum=WGS84). The world_buffer is the world's coastlines, found here, with a 5 km ...
1
vote
1answer
39 views
Inserting values in a table depending on spatial relation between objects in to tables
I have a new and empty column in a table and I want to insert values, using postgis depending on the spatial relation between the geometries in two tables.
I have one table (points) with benches and ...
4
votes
1answer
87 views
How to optimize PostGIS Intersection Query
I've 2 sets of polygons in 2 tables and the sets overlap each other. For each polygon in set A, I get the ID of the polygon in set B that it overlaps the most.
This is the query:
SELECT DISTINCT ON (a....
1
vote
1answer
68 views
Snapping end points of line to point using PostGIS
I have a problem with over 10k lines from linetable that do not end/start in a point from pointtable.
I need to get these lines snapped so I tried to intersect them by using st_snap
update linetable c ...
0
votes
1answer
35 views
Wrong result when clip a raster with PostGIS
I'm newbie of GIS, I have my first project with GIS without experience before.
I want to clip a zone of raster for the calculation of NDVI index. It is ok when I did it with GDAL and TIFF file. But I ...
2
votes
1answer
56 views
PostGIS: problem with point count result when they are over the polygon edge
I have a PostgreSQL 12 database with PostGIS (using EPSG 4326). There are some tables with millions of points, and others with polygons.
In general , I had no problems with the queries. However, I ...
3
votes
2answers
167 views
Performance issues with PostGIS ST_Intersects
I have a couple of tables in PostGIS that are big (~4 million rows). The geometry columns in both the tables are indexed. But I find a huge difference in query times between ST_Within and ...
0
votes
0answers
16 views
Adjusting polygons layers's boundaries in PostGIS [duplicate]
The case is quite typical. I’ve to compare two (multi-)polygons layers of administrative regions, with l2 being lower level (let’s say municipalities – 2811 records) than l1 (let’s say provinces – 785 ...
1
vote
0answers
125 views
Using spatial indexes on point to polygon intersection query in SQL Server
I have a table (tab_point) with about 5 million points and another table with a few thousand polygons (tab_polyg). I need to find what points intersect with what polygon (to establish a count of ...
0
votes
0answers
34 views
PostGIS ST_Intersects or ST_Within for Point in Boundary
When I query with ST_Intersects or ST_Within I get the same result. I want to know which one to use for performance wise? Note: For points, there is no difference.
Using ST_Within:
EXPLAIN ANALYZE
...
1
vote
0answers
128 views
PostGIS: Speed up intersect by filtering first
I have a table with 20 million polygons where queries take some time (with an gist index on the geometries). I thought I could speed up the queries by first filtering on an additional id (selectivity: ...
3
votes
2answers
77 views
How to get the area of two interesecting polygons divided into attributes on PostGIS
I am trying to calculate the area overlap two vector polygon layers.
I have the one based layer - soil map and I have second layer - plots.
The example below:
I would like to know how much area each ...
2
votes
0answers
267 views
Create spatial joined table with intersected geometry using ST_Intersects and ST_Intersection in PostGIS
I want to perform a spatial intersection join of the two tables "temperature_precipation_slope" and "buek200_clc10 with ST_Intersects in PostGIS via psycopg2 in Python and write the ...
2
votes
1answer
120 views
ST_Intersects slow across two tables
I have a set of municipality maps, with an ID for each municipality, and a Multipolygon for the geometry. In PostGIS, I've put these data into tables. Table 1 has the municipalities in year 2019 (each ...
3
votes
2answers
400 views
PostGIS Spatial Relationships query performance
I have a very large dataset contains over 700 million points and a polygon dataset as a buffer zone.
My task is to extract all points inside the buffer zone and create a new table.
Below is my code. I ...
2
votes
1answer
151 views
Find COUNT of multiple tables that ST_INTERSECT a main table
I have 3 tables. 1 polygon main table polytable (400,000 polys with 1000 unique names in the polyname column), and 2 point tables pointtable1 (millions of points) and pointtable2 (1000s of points). I'...
6
votes
1answer
461 views
PostGIS ST_Intersects does not return expected result
I want to search objects that have at least one point inside of a rectangle. Based on the size of the rectangle, PostGIS sometimes returns the contained object, sometimes not.
create table t1 (id ...
1
vote
0answers
77 views
Efficient Way of Getting Intersections Between Segments and Shapes in Postgis
I have a set of 7849 tridimensional segments and 71 tridimensional shapes. My goal is to count the number of intersections between the segments and the shapes using Postgis. The segments were modeled ...
0
votes
1answer
86 views
Linestring table (of MultiLinestrings) ST_Intersection
I'm moving from GeoPandas to PostGIS and GeoAlchemy.
I've a table full of multilinestring geometries (timezones from NaturalEarth.com) and I've a linestring which represents what will be the route of ...
7
votes
4answers
602 views
Count number of points by name within distinct polygons [closed]
In PostgreSQL/PostGIS I have 2 tables:
A POLYGON table (polytable1) with shapes named 'Poly 1', 'Poly 2' etc... Each name can appear multiple times (i.e. 20 polygons with the name 'Poly 1')
A POINT ...
2
votes
1answer
386 views
ST_Intersects with multiple geometries
I am just learning PostGIS and have a simple problem I can't get the answer for.
I want to select the geometries that intersect with given multiple geometries.
To make it simpler, let's say I want ...
3
votes
2answers
1k 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
1k 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 ...
3
votes
1answer
342 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
297 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
218 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
38 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
41 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
434 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
116 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
144 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
174 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
907 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
230 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....
3
votes
2answers
754 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
238 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
...
2
votes
2answers
161 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 ...
