All Questions
2,007 questions
2
votes
1
answer
262
views
Selecting polygon if their centroid falls in another polygon using PostGIS?
I'm trying to select polygons which centroids fall in another polygon:
SELECT smaller_polygons.id
FROM big_polygon
LEFT JOIN big_polygon smaller_polygons
ON ST_Within(ST_Centroid(smaller_polygons....
- 77.9k
6
votes
2
answers
155
views
Filtering by secondary attribute in PostGIS. Issues with NULL
I'm new to PostGIS. I'm trying to perform a dissolve operation on polygons depending on a particular attribute fields called priFunc and priForm.
My SQL query is
SELECT ST_Union(geom) AS new_geom, ...
- 77.9k
2
votes
1
answer
464
views
Merging polygons using PostGIS
I need to do a simple merge polygon in PostGIS. The attribute table from both input shapefiles needs to be preserved. This is easily done In QGIS by using the merge polygon tool. But I don’t know how ...
- 77.9k
2
votes
1
answer
64
views
PostGIS query on having their centroid in the source layer feature
I am trying to port the ArcMap query by location ("Have their centroid in the source layer feature") to PostGIS. The aim is to select all feature that are completely within and touches the ...
- 77.9k
5
votes
1
answer
493
views
Calculating shortest distance between two tables using PostGIS
I am trying to calculate shortest distance from each point to polygon where idfield match and add the distance to a field in the point table:
For the two "A" points the distance is the same,...
- 77.9k
1
vote
1
answer
535
views
Select domain codes/descriptions using XMLTABLE instead of EXTRACTVALUE
Oracle 18c 10.7.1 GDB:
The ArcGIS system table docs have an example for extracting domain codes and descriptions from XML:
Example: Resolving domain codes to description values using SQL
--simplified:...
CommunityBot
- 1
2
votes
1
answer
72
views
Querying layer in QGIS Model Designer
How can I create a query in a layer using the Model Designer in QGIS, like the example shown in the picture below?
How i can make this in Model Designer?
- 77.9k
12
votes
2
answers
8k
views
What are benefits of using ArcGIS with PostgreSQL over simply ArcSDE?
I understand that in order to benefit from ArcGIS spatial datatypes (ST_GEOMETRY, versioned geodatabases etc.) ArcSDE needs to be installed - and with the 10.1 client it is automatically installed (...
- 41.6k
12
votes
2
answers
5k
views
How to UPDATE with LATERAL Nearest-Neighbour query?
Say I have table gps like:
CREATE TABLE gps(
gps_id serial primary key,
measured_timestamp timestamp,
geom Geometry(Point,4326),
stop_id int --Null
);
And I have a table of stops like:
...
- 30.9k
0
votes
2
answers
1k
views
Quering out multiple values in MapInfo SQL Query
I am trying to filter out around 1000 rows and delete them in MapInfo as they are no longer required in the data set. If there was a smaller quantity then I would use an SQL Query but it has its ...
1
vote
1
answer
878
views
Splitting polygon into separate polygons using table of individual lines
I have been trying for a while but have not been able to split a polygon at multiple locations, using a table of lines, to get what is on the right side of the above image.
The best I can achieve is ...
CommunityBot
- 1
2
votes
1
answer
832
views
"Unable to find driver `MSSQLSpatial'" with ogr2ogr in Windows
I am trying to import an ESRI shape file into SQL Server 2019 under Windows 11.
I have a problem similar to Attempting to use ogr2ogr to convert shapefile to SQL Server table; getting error "...
CommunityBot
- 1
38
votes
21
answers
35k
views
Connecting to PostGIS database from ArcMap for display and query without ArcSDE?
How can I connect to a PostGIS database from ArcMap using ArcGIS Desktop 9.3 and later?
I would like to be able to perform spatially enabled queries and receive the results back (e.g. spatial and non-...
- 20.3k
2
votes
1
answer
631
views
Find all closest points to unique point in PostGIS
I have two tables valid_collisions (POINT) and intrct_pts (POINT)
I want to create a new table using a nearest-neighbor type of query to find aggregate values from my valid_collisions. In essence I am ...
CommunityBot
- 1
0
votes
2
answers
88
views
"AttributeError: ArcSDESQLExecute: StreamExecute ArcSDE Extended error 900 ORA-00900: invalid SQL statement" error when running SQL query via ArcPy
I am trying to run a SQL query in ArcGIS Pro ArcPy and I am getting errors. I have looked into the ArcSDESQLExecute sample query.
https://pro.arcgis.com/en/pro-app/latest/arcpy/classes/...
- 20.3k
0
votes
2
answers
968
views
Sampling points in R with both distance constraint and group
I have a spatial point data frame with categorical variable as a column named "type", I would like to draw a random sample from these points (let's say 100 points) with maximum and minimum ...
CommunityBot
- 1
1
vote
0
answers
347
views
Limiting loaded feature from WFS to the extent of feature in another layer with SQL
I am trying to download all the features from a WFS layer (Land_Parcel_Layer) and save them locally. The layer contains the land parcels for the whole federal state in Germany. The service URL is: ...
- 34.1k
1
vote
1
answer
173
views
Selecting polygon feature classes with one record in Oracle geodatabase using SQL query?
I have an Oracle Enterprise geodatabase with many feature classes. I would like a SQL query to find all the polygon feature classes with only one record in its table. So far, I can only query all the ...
- 4,119
5
votes
1
answer
97
views
Difference of attributes between two shapefiles based on similar field in QGIS
My issue is best shown using tables (the issue is only with shapefiles in QGIS):
pre-culvert.shp:
element_no
depth
1
10
4
3
post-culvert.shp:
element_no
depth
30
44
4
6
Looking for the ...
- 77.9k
3
votes
1
answer
73
views
In PostgreSQL how to build polygons using lines around each dot
Probably a bit hard to explain but hopefully the image can explain better
There are 2 datasets, one is a point, the other is a line. Intended outcome is having polygons based on them
The lines form a ...
- 77.9k
3
votes
2
answers
191
views
Changing values where two geometries are equal otherwise keep value with QGIS Virtual layer
I have two line layers in QGIS, of which one layer (layer 2 - yellow in the image) is a geometrical subset of the other (layer 1- blue in the image). The two layers also differ in their values of a ...
- 34.1k
1
vote
1
answer
37
views
gpkg filename to add PostGIS table field
Is there a way in ogr2ogr to add gpkg filename to a PostGIS table existing field (filename)? during append?
ogr2ogr -f "PostgreSQL" PG:"host='ip' port='port' user='user' dbname='db' ...
- 66.5k
0
votes
1
answer
33
views
ST_GEOMETRY SQL function to find problem annotation shapes
ArcGIS Pro 2.9.5; Oracle 18c 10.7.1 EGDB; SDE.ST_GEOEMTRY:
I have a issue with an annotation feature class:
In 2.9.12, I get a "shape integrity error" when I open the attribute table and ...
- 34.1k
4
votes
1
answer
134
views
Merging PostGIS tables with billions of rows
I'm trying to create a unified high population density dataset using the Facebook "data for good" dataset. Currently their dataset is available as a raster or CSV per catagory (men, women, ...
- 787
0
votes
2
answers
618
views
ArcGIS Pro: Definition Query > Create new definition query in SQL
I have one point type feature class and another feature class with a polygon.
Using ArcGIS Pro, in the feature class type point > Definition Query > create new definition query in SQL.
I want as ...
CommunityBot
- 1
1
vote
1
answer
430
views
Correct syntax for filtering of geopackages (SQL)
I already asked about this topic, but I am still struggling.
I created a geopackage with railways of Germany. I need to filter some sections out. I understand that geopackages work with SQL syntax for ...
CommunityBot
- 1
6
votes
1
answer
166
views
Joining points using PostGIS
I have a set of points which represents a series of telegraph poles. I want to link them to simulate the wire running along them. The code I've been using is shown below.
SELECT DISTINCT ON (p1.id)
...
CommunityBot
- 1
3
votes
1
answer
161
views
Filtering subset of records to be used in subsequent SQL statements
I recently posted this question, where the answer involved SQL and a Virtual Layer. In that situation, the SQL acted on the entire input layer, with the output exactly as desired.
Now I would filter ...
- 34.1k
5
votes
3
answers
274
views
Converting multipart MultiPolygon to self-intersecting polygon in PostGIS
Is it possible to convert a MultiPolygon to a polygon when the result is a self-intersecting polygon?
I have a bunch of MultiPolygons that are essentially originally formed to be multipolygons because ...
- 34.1k
3
votes
2
answers
376
views
Convert linestringz to linestringm using alter column?
I am attempting to import linestring data with or without m values, then test whether the data has m values, then add m values to the geometry if they are missing. So far, I have working tests for ...
- 7,612
0
votes
2
answers
883
views
Copying raster to SQLite using GDAL?
I want to copy a raster file into SQLite database. Previously, I did the same with vector data like this:
def store_vector_output(self, file_name, identifier):
from osgeo import ogr
drv = ogr....
CommunityBot
- 1
1
vote
0
answers
40
views
Import shapefile using ogr2ogr with a subselect to fill a constraint fails to "cannot open datasource"
I try to import various shapefiles into a postgis database. The shapefiles have some foreign keys referencing other existing (and already successfully imported) tables.
ogr2ogr -f "$($json....
- 111
0
votes
0
answers
59
views
Get vertex of SDE.ST_GEOMETRY polygon using SQL
Oracle 18c 10.7.1 EGDB:
I want to write a SQL query that has rows with the XY coordinates of each vertex of an SDE.ST_GEOMETRY polygon feature class.
I can do it for a line FC using a cross join, a ...
- 1,282
0
votes
0
answers
37
views
Create virtual layer out of three tables
I have a problem with creating a virtual layer.
The issue is from a bad Database where the line geometry id is linked to non-geometry tables where the other information is included. In case this is a ...
- 20.3k
3
votes
2
answers
11k
views
Execute sql query from QGIS
I have to tables inside my database (postgis). Now I will do based on QGIS execute a sql-query based on these two tables. How can I do this?
There exists a tool "Execute SQL" but there I can only ...
- 823
0
votes
0
answers
140
views
Calculating road condition score percentages for each zip code with SQL query in QGIS
I have a shapefile that holds information about the condition of a road network that runs across several cities (zip codes):
The road network is split into segments of various lengths (meters)
Each ...
- 65.4k
2
votes
1
answer
70
views
Changing geometry column data type in all tables of a schema
I have a database of almost 500 tables. For most of them, the type is set to 'GEOMETRY'. I need to switch the type to be POINT, LINE or POLYGON.
Using this query :
select f_table_name, ...
- 30.9k
3
votes
1
answer
308
views
How to fix "failed to prepare SQL" error when creating GPKG file from OSM extracts using ogr2ogr?
I downloaded an OpenStreetMap extract for Europe, to a file latest.osm.pbf:
curl https://ftp5.gwdg.de/pub/misc/openstreetmap/download.geofabrik.de/europe-latest.osm.pbf \
-o latest.osm.pbf
I would ...
- 2,690
1
vote
1
answer
64
views
Ogr2ogr Select statement to define new geometry out of given coordinates
I would like to extract and alter place label coordinates from a GML file with cadastral parcel information using ogr2ogr. See part of the GML file at the end of this question.
Besides the given place ...
- 69.2k
2
votes
1
answer
337
views
Convert WKB geometry to WKT
I am using DuckDB in order to access buildings polygons (an open dataset).
query = f"""
SELECT
type,
height,
level,
class,
JSON(names) as names,
JSON(sources) as ...
- 11.3k
1
vote
0
answers
72
views
Layer creation by running a SQL query in DB manager tool (QGIS)
I just would like to know if there is a way to create a new layer among the virtual layers of a QGIS project, by running a CREATE TABLE statement in the DB manager tool?
- 27k
2
votes
1
answer
86
views
Aggregate/group points by value, and label aggregated point with aggregate expression (QGIS)
Problem Statement
I have a layer of multiple points, and I am trying to:
Aggregate/group the points by their attribute values, so that one point is drawn for each group of points with matching ...
- 35.6k
3
votes
1
answer
104
views
Updating full table instead of matched record problem
I am writing a PostGIS query in which I have to update the geometry column in table A with the value of the geometry column in Table B.
Table B contains only 300 Rows while Table A contains more than ...
- 787
1
vote
1
answer
706
views
Mapinfo SQL Query for comparing edited data in tables
I have a table that contain several columns, each has their own data. then, someone have made some change in the data (just a little one data editing).
How do I know later what data have been edited/...
- 65.4k
1
vote
2
answers
115
views
ST_Distance from one location to others
I have a table that contains different city buildings and
want to calculate the distance of all the buildings of a type to another with a specific id using the ST_DISTANCE() function
- 83
1
vote
1
answer
152
views
Use ST_Distance() to filter a maximum of distances
I've tried to select only the schools/kindergarten that are maximum 1000m distant from the train station. Here is my code
SELECT gebaeude.name AS "Name",
ST_Distance(geom,(SELECT geom FROM ...
- 83.2k
2
votes
1
answer
119
views
intersect with ST_Buffer in PostGIS
I have a table called "gebaeude" with all types of buildings in the city.
In the following I want to use a Buffer around the central train station that has the id="2" and a radius ...
- 83
2
votes
0
answers
58
views
Calculating risk factors for waste management system using SQL database
I am working on a waste management system where I need to prioritize actions based on risk factors associated with different types of waste, including industrial and sewage waste. The system utilizes ...
- 20.3k
6
votes
3
answers
1k
views
Grouping points to clusters containing similar number of features using QGIS
The question
Using QGIS, how to cluster unevenly distributed points from the same layer to groups of about the same size / containing the same number of features?
The problem
I have a large point ...
- 17.9k
1
vote
1
answer
162
views
OSM Vector tile ST_AsMVTGeom SQL query Coordinates error
I am currently working on developing a Node.js API with Express to retrieve vector tiles from a PostGIS database.
I am using the ST_AsMVTGeom function to transform geometries into vector tiles, but I ...
- 4,576