All Questions
2,007 questions
0
votes
1
answer
73
views
Summing up values of polygons within bigger polygon in QGIS
I have buildings as polygons with a lot of data in the attribute table. My goal is, to sum up the values selected for a table column of all building polygons within the building blocks (here in purple)...
- 745
0
votes
0
answers
13
views
GeoNetwork : Extract Organisation names defined for all records
With GeoNetwork, I have more 2000 records into the catalog. I need to extract all values currently defined for the Organisation names. With pgAdmin, I have success in querying without error message :
...
- 919
1
vote
1
answer
44
views
QGIS expression to symbolise road condition between values
I am using QGIS 3.22.16. I try to symbolize a road condition. The result wanted is:
Equal 2 if Minor > 0.3
Moderate > 0.2
severe > 0.1
Equal 1 if
0.2 < Minor <0.3
0.1 < Moderate <...
- 360
0
votes
0
answers
17
views
How to change an ogr2ogr KML Placemark Name?
If I use
ogr2ogr -f LIBKML m32540000.parcel.kml ../../3254.kml -ct \
'+proj=affine +xoff=-.00005 +yoff=-0.00004' \
-nln land -clipdst mChopper.kml -clipdstlayer mChopper \
-clipdstwhere "Name = '...
- 568
1
vote
1
answer
56
views
Join points into lines, split by a date gap
I have data for the location and dates of a band's gigs as point features. I want to turn these into line features where the lines come from all points in time-order until there's a gap of a given ...
- 66.5k
1
vote
0
answers
48
views
SQL approach to forms layout in QGIS
We're creating GeoPackage to use in QField. In the Attributes Form we have a Category-field where you can choose a value from a look-up table (layer). And a separate field for Sub-Category, which is ...
- 113
2
votes
1
answer
70
views
Get position in meters of POI on a polyline
I have a table of POIs which are simple Points
I would like to know each time a POI is close to the polyline. I would like to get the number of meters from the start of the polyline each time a POI is ...
- 123
0
votes
0
answers
69
views
Using UNION ALL with view table
I have table in multiple schemas, the table in all schemas have the same columns number and type so i'm trying to combine all table data into view table using UNION ALL as shown
CREATE VIEW "...
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?
- 21
2
votes
2
answers
96
views
QGIS Dynamic filter based on the names of layers visible within a group
I'm not sure if this is even possible in QGIS, but I try to filter features in a layer based on the names of visible layers within a certain group.
Example:
I have a group holding layers named by ...
- 97
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/...
- 49
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 ...
- 53
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 ...
- 2,350
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 ...
- 1,853
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' ...
- 1,519
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, ...
- 1,653
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 ...
- 8,386
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 ...
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 ...
- 1,282
5
votes
3
answers
273
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 ...
- 51
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, ...
- 81
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
5
votes
2
answers
292
views
Problem with ST_Difference in PostGIS
I'm testing various SQL queries to do a seemingly easy ST_Difference in PostGIS. It needs to be done in SQL as I'm testing to later use the query on quite large datasets.
Original code I tried:
CREATE ...
- 135
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 ...
- 2,215
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?
- 11
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 ...
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 ...
- 135
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 ...
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 ...
- 21
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 ...
- 133
0
votes
0
answers
72
views
SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended"
I was trying to update one table based on the spatial join table. When I try to run following code:
UPDATE p
SET
p.mileage = ip.length_unusually
FROM
PIPELINE p
JOIN
...
0
votes
0
answers
78
views
Creating non-overlapping boundary polygons with point attributes
I have a set of points that represent the meter locations for my electric company. These points contain a 3 digit code that represents the route number for the meter reader routes. I also have a ...
2
votes
1
answer
117
views
Create a raw SQL statement ready to be used in PostGIS from a geometry in QGIS
Given an existing geometry in QGIS 3.28+ (e.g. from a temporary scratch layer), is there a quick way (i.e. with the lowest amount of user actions) to generate the PostGIS geometry definition (raw SQL ...
- 10.9k
2
votes
1
answer
58
views
Compare field attributes
I am using ArcMap 10.8.2. I have two point layers from different sources which contain the educational facilities of Catalonia. Each layer may contain facilities that are not contained in the other ...
- 143
2
votes
1
answer
84
views
Creating autoincremental ID for groups in QGIS
I have an attribute table containing information about area, region, and samples. The table structure resembles the example shown below:
My goal is to automate the generation of "ID"s when ...
- 518
0
votes
1
answer
68
views
How do I set the result of a SELECT query equal to a new column in a GPKG file using ogrinfo?
This thread: How do I add a new column to a GPKG file using ogrinfo? explains how to add a new column to a GPKG file and set all rows in it to a specific constant value. Here, I would like to add a ...
- 2,690
3
votes
1
answer
63
views
Joining two polygon tables based on conditions
I have two tables: table1 and table2, I would like to join polygons from table1 (purple) to table2 (yellow) if the polygons in table1 don't intersect or are coveredby table 2 polygons. I would like ...
- 1,015
2
votes
2
answers
65
views
How to inverse spatial query [duplicate]
I have two tables with polygons (CTR_daim and CTA_LOW_1111). I have this query to select all polygons from CTR_daim that is intersected by any of the polygons from table CTA_LOW_1111
WITH
...
- 475
4
votes
1
answer
82
views
ST_CLIP with one raster and one polygon
I don't understand how I can select a raster and polygon by ID and then clipping them.
Something like:
SELECT ST_CLIP(selected_raser.rast, 1, selected_polygon.geometry)
From
raster WHERE ID = 99 ...
- 250
0
votes
0
answers
275
views
SQL access to GEOPackage data
I am new to the world of QGIS. I work for a company that records birds in order to prepare expert reports. I am actually a programmer and would like to make my life a little easier when analysing the ...
0
votes
0
answers
87
views
How do I create a Geodataframe which the data is grouped by User ID & the timestamp is sorted from oldest to most recent?
I've been struggling to complete this exercise (Exercise 2 Problem 3) which you guys can refer to this link. A reminder that the kruger_points.shp is obtained from Exercise 2 Problem 2. Here's the ...
- 47
0
votes
0
answers
20
views
Access control point vertices using SQL
ArcGIS Pro 3.2.1; Oracle 18c 10.7.1 EGDB; SDE.ST_GEOMETRY
Regarding control points:
Video: Suppress symbol effect in ArcGIS Pro
Introduction to control points
CIMGeometricEffectAddControlPoints Class
...
- 1,282
3
votes
1
answer
160
views
How to group/clean group of scattered points based on their distance and attributes?
I downloaded traffic signs from Mapillary API. I found that many of these points represent the same real life entity, but they are scattered in the map. Since they are distinguished by their category ...
- 645
4
votes
1
answer
414
views
Which function should I use to find polygon overlaps with other polygon?
I need a spatial function to detect given polygon is overlapping with existing polygons in the database.
I tried to use ST_Intersects(). It returns true if polygons touch each other. But In my case ...
- 143
3
votes
0
answers
92
views
Are there ways to speed up building large topology datasets in PostgreSQL/PostGIS?
I used this stored procedure to create a topology for all Census block groups in the contiguous US. It took almost 136 hours to run (on a fast, dedicated workstation - Ubuntu 23.04, PostgreSQL 16, 128 ...
- 187
1
vote
1
answer
90
views
Point shapefile has duplicate labels in QGIS
I have a point shapefile with a attribute table which has many rows. In the 'Location_Names' column there are occasional duplicates since some people live in the same location. Consequently, there are ...
- 253
0
votes
0
answers
54
views
SQL loops and query
I need to apply an ST_distance query to find distance from the given table. How should I tell my query to take points one after the other consecutively on its own or like if the teacher specifies to ...
- 1
3
votes
2
answers
157
views
Why following ST_Intersects SQL returns false
I run following code in PostGIS. Both SRID is default, which is 4326.
select ST_Intersects('POINT(104.042455 30.657381)'::GEOGRAPHY(POINT), ST_MakeEnvelope(80, 30, 140, 40, 4326)::GEOGRAPHY(POLYGON))
...
- 125