Skip to main content

Questions tagged [sql]

Acronym for Structured Query Language. A syntax for retrieving and manipulating data from a relational database.

Filter by
Sorted by
Tagged with
1 vote
1 answer
42 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 ...
Luffydude's user avatar
  • 2,328
3 votes
2 answers
167 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 ...
i.i.k.'s user avatar
  • 1,753
1 vote
1 answer
28 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' ...
Tamas Kosa's user avatar
  • 1,509
3 votes
1 answer
103 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, ...
RedM's user avatar
  • 1,565
3 votes
1 answer
132 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 ...
Stu Smith's user avatar
  • 8,192
1 vote
0 answers
34 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....
semTex's user avatar
  • 111
0 votes
0 answers
25 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 ...
User1974's user avatar
  • 1,172
0 votes
0 answers
26 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 ...
Gafzgarrr's user avatar
0 votes
1 answer
20 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 ...
User1974's user avatar
  • 1,172
5 votes
3 answers
161 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 ...
ambivalent's user avatar
2 votes
1 answer
59 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, ...
Abigail's user avatar
  • 81
3 votes
1 answer
223 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 ...
zabop's user avatar
  • 2,234
5 votes
2 answers
193 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 ...
vivjmin's user avatar
  • 135
0 votes
1 answer
155 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 ...
ReutKeller's user avatar
  • 2,169
1 vote
0 answers
44 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?
Eilin's user avatar
  • 11
1 vote
1 answer
49 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 ...
Sebastiaan's user avatar
3 votes
1 answer
99 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 ...
Muhammad Kamran's user avatar
2 votes
0 answers
53 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 ...
user240504's user avatar
1 vote
1 answer
106 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 ...
AhmeX2's user avatar
  • 123
0 votes
0 answers
53 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 ...
Nimisha Wagle's user avatar
2 votes
1 answer
96 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 ...
swiss_knight's user avatar
  • 10.5k
2 votes
1 answer
48 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 ...
JoanB's user avatar
  • 143
0 votes
1 answer
52 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 ...
zabop's user avatar
  • 2,234
3 votes
1 answer
56 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 ...
MJM's user avatar
  • 995
2 votes
2 answers
57 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 ...
Paul's user avatar
  • 475
4 votes
1 answer
60 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 ...
Squeezie's user avatar
  • 250
0 votes
0 answers
148 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 ...
Harry1980's user avatar
0 votes
0 answers
18 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 ...
User1974's user avatar
  • 1,172
4 votes
1 answer
238 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 ...
hurelhuyag's user avatar
3 votes
0 answers
79 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 ...
Stonetip's user avatar
  • 187
1 vote
1 answer
82 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 ...
Lina's user avatar
  • 233
0 votes
0 answers
48 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 ...
Ayman's user avatar
  • 1
3 votes
2 answers
153 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)) ...
timewalker's user avatar
1 vote
0 answers
52 views

QGIS - layout/ print composer and linking displayed maps

I wonder if it's possible to display a map in the layout/print composer when another is shown ? I have this layer where I've made some statistics with diagrams. It shows the percent of people that are ...
Sarah BOUTARD's user avatar
0 votes
0 answers
35 views

The jsonb field does not work correctly in QGIS

I created a trigger that should automatically populate the field, but for some reason in QGIS 3.16, the value is not displayed, only the key is populated. The key shows 09132000-3.1.3 14210000-6.3.3 ...
Miwasik's user avatar
  • 89
1 vote
1 answer
174 views

Buffer/expand point contained inside another polygon

I have a point at one corner of a square. I want to buffer that point with a specified size, but I don't want the buffer to extend beyond the boundary of the polygon behind it. The buffer should ...
Lars Anders Myklebust's user avatar
0 votes
0 answers
70 views

Triggers for multiple tables in Geopackage and DB Browser for SQLite for QGIS use

I need to build an automatic trigger in the database so that when inserting/updating a new polygon in the SETORES feature and filling in the CD_GEOCMU key attribute with the appropriate code, for ...
Denilson's user avatar
  • 695
0 votes
0 answers
75 views

Edit PostgreSQL database with ArcPy

I have an ArcPy script that works locally with a .gdb database and create some features in this local DB. I would like to migrate my infrastructure on a server and do the same work but on a PostgreSQL ...
fed's user avatar
  • 27
2 votes
2 answers
75 views

Check if polygon falls inside other polygon with 2 layers

I have two polygons layers with SRID 4326, indexed in a PostGIS database. Layer 1 is a country layer. I want to check whether the polygons (> 60k instances) from layer 2, fall inside the boundaries ...
GIStrees's user avatar
  • 119
2 votes
1 answer
91 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 ...
Alm's user avatar
  • 83
1 vote
1 answer
104 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 ...
Alm's user avatar
  • 83
1 vote
2 answers
96 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
Alm's user avatar
  • 83
0 votes
0 answers
66 views

QGIS how to improve triggers on SQL - SpatiaLite

I'm looking for a way to improve the way my triggers are working on a SpatiaLite layer. Currently, it takes 45 seconds to work on 1000 entities. Is there a way to improve the performance? My triggers ...
Giene's user avatar
  • 763
1 vote
0 answers
43 views

How to define the points in ST_PointFromText command based on lon lat field

I have a filename with the columns: lon, lat, val1, val2 etc. and I want to join it with a grid shapefile in WGS84. I use the following command but I get an error in ST_PointFromText ogr2ogr -sql &...
nat's user avatar
  • 289
1 vote
1 answer
90 views

SVG Symbol in Attribute Table

i have few hundred records in table, i need to select them and add SVG symbol to correct one in QGIS. I created new rule in in attribute table, then i wrote a simple rule (thats only two of much more) ...
RebiF's user avatar
  • 41
1 vote
0 answers
56 views

Refresh attribute table after SQL trigger

I'm utilizing a trigger on a SpatialLite layer in QGIS. This trigger is designed to update a field when its content matches with another in the database. Here's the trigger's code: CREATE TRIGGER ...
Giene's user avatar
  • 763
3 votes
1 answer
92 views

PostGIS fields of type interval not part of QGIS' field list

I have calculated a flight trajectory dataset from open data which has a couple of fields that can be used in GIS (3.32) without any troubles. but there seems to be a problem with fields of type "...
robert tuw's user avatar
2 votes
0 answers
55 views

How do I find the length of part of a line segment that crosses a polygon boundary?

I have a street centerline table and a borough boundary polygon table. I want to select any centerlines that cross borough boundaries and also return the how much of that centerline is crossing each ...
Kurt Cederholm's user avatar
0 votes
1 answer
100 views

PostgreSQL and extension tablefunc with enumerated type

In PostgreSQL, I created an enumerated type CREATE TYPE w_sebio.t_sebio_type_sad as ENUM ('transféré', 'traité', 'non soumis', 'ouvert', 'non traité','PAC réalisé'); then a table CREATE TABLE w_sebio....
fcka's user avatar
  • 985
0 votes
0 answers
52 views

Ogr2ogr Clipping data using SQL Server database object for input and overlay

I am trying to understand the ogr2ogr comands for clipping. At this moment I want to clip data using SQL Server database object (dbo) It did try but no success. ogr2ogr.exe -f "GPKG" "...
NvBgm's user avatar
  • 131

1
2 3 4 5
37