Questions tagged [sql]
Acronym for Structured Query Language. A syntax for retrieving and manipulating data from a relational database.
1,823
questions
0
votes
0
answers
49
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 ...
0
votes
0
answers
49
views
SQL query in QGIS 0 rows affected [closed]
I am new in QGIS.
I have in QGIS vector layers loaded. Two of the four layers have the same CRS. For another two, I must transform between the two CRS. Now when I am doing some query in SQL between ...
0
votes
0
answers
32
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 ...
- 695
0
votes
0
answers
36
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 ...
- 9
1
vote
2
answers
66
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 ...
- 109
1
vote
1
answer
42
views
How to intersect with ST_Buffer in PostGIS
I have a table called "gebaeude_salzburg" 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 osm_id="...
1
vote
1
answer
57
views
Use ST_Distance() to filter a maximum of 1000m
I've tried to select only the schools/kindergarten that are maximum 1000m distant from the train station. Here is my code
SELECT gebaeude_salzburg.name AS "Name der Einrichtung",
...
1
vote
2
answers
58
views
ST_Distance from one location to several locations on the same table
I have a table that contains different city buildings called "gebaeude_salzburg".
I want to calculate the distance of all the buildings of the type='school' and 'kindergarten' to the central ...
0
votes
0
answers
58
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 ...
- 489
1
vote
0
answers
34
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 &...
- 289
1
vote
1
answer
29
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)
...
- 41
1
vote
0
answers
34
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 ...
- 489
3
votes
1
answer
62
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 "...
- 888
2
votes
0
answers
20
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 ...
- 157
0
votes
1
answer
77
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....
- 899
0
votes
0
answers
44
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" "...
- 131
2
votes
1
answer
37
views
Representing polygon features using arrows that represent direction of orientation?
I have a polygon layer that stores results obtained from simulations run on a hydrological modelling software, ICM.
A particular table within the attribute table of this polygon layer has details ...
4
votes
0
answers
47
views
In QGIS, can the 'spatialite execute sql' algorithm accept '.sql' text files?
I'm working in Model Designer on a process which includes running an SQL on a SpatiaLite database. I have the connection as the input, but I want the SQL to come from a text file 'xxxx.sql'. ...
- 41
3
votes
1
answer
88
views
QGIS Speed up slow virtual layer
In QGIS I have a virtual layer which does a line generation based on a point shape. The point shape has about 700 point geometries and 26 attribute fields.
SELECT a.Name, a.FromNode, a.ToNode, a....
- 61
5
votes
1
answer
100
views
Creating LineString from points by group of time period using SQL
I have a serie of GPS points with dates and a state ('flying' or 'grown') from different individuals.
I would like to get lines from those points by individual, grouped by state BUT still ordered by ...
- 153
2
votes
0
answers
56
views
PostgreSQL 'UNION ALL' statement in a view does not load in QGIS
I have two datasets, sharing the same attributes (but being distinct from each other), each in their PostGIS table. The tables are huge (both geometrical and containing ~1 billion features) and have ...
- 71
2
votes
1
answer
75
views
st_generatepoint returns invalid memory alloc request size 4305734400
As a result of some analysis I am left with this polygon (there are probably others, but this is one example):
SELECT st_astext(geom,1) FROM flood.temp WHERE id = 9695
POLYGON((29.8 52.2,29.8 52....
- 503
2
votes
0
answers
37
views
PostGIS selecting points using a subset of lines using ST_DWithin
I'm not sure why this script sends a much higher count than the real number. This one sends 2314 points, which the answer should be 114
I can get the correct value by either dissolving all the ...
- 101
1
vote
2
answers
85
views
Create new layer in QGIS using SQL query
This is a related question to: Speed up performance of virtual layer based on GeoPackages in QGIS
In that question, I was trying to use a virtual layer in QGIS (3.30) to add a region field to a suburb ...
- 3,860
1
vote
0
answers
93
views
"Shape Integrity error" on ArcMap when reading from PostGIS database [closed]
I have a query that creates Polygons/Multipolygons from union of a polygons from table in postgres using postgis. The spatial objects created all read as valid in postgis and all are Polygons/...
- 11
0
votes
1
answer
42
views
Joining CSV to gpkg file with ogr2ogr: 'no such table' error
I'm trying to follow the accepted solution from this post: Join CSV file to shapefile using gdal/ogr
But I keep getting the error "no such table". My CSV does in fact exist. Can somebody ...
- 593
2
votes
1
answer
87
views
Is there a SpatiaLite equivalent to PostGIS' ST_DumpPoints? [duplicate]
I've been using PostGIS' ST_DumpPoints to 'explode' multipolygon layer into points with its corresponding polygon part, ring and index information. As QGIS uses a SpatiaLite engine against its Virtual ...
- 1,132
3
votes
1
answer
105
views
Find out which polygons that are inside a surrounding polygon
I have a table with a lot of polygons, some polygons are inside a surrounding polygon. I would like to set the table attribute "inside" to "1" if it's a polygon that are surrounded ...
- 407
1
vote
1
answer
65
views
What's wrong with my spatial SQL queries?
I have 3 tables within the sample.gpkg, descirbes as following:
sample.gpkg
-- parcel [polygon]
----------------
fields:
- landuseType
- landuseZoning
- ...
- 355
0
votes
0
answers
33
views
DB2 ST_AREA function with SRID: 4326 returns wrong units
On any other database system with a an SRID of 4326, ST_Area returns a value in meters. However, DB2 returns decimal degrees by default. I get that I can specify meters as an argument but why return ...
- 15
3
votes
1
answer
116
views
Date management problem in a trigger with PostgreSQL [closed]
I created two tables on PostgreSQL.
I would like to note in a layer a date and retrieve:
the corresponding year in a variable,
then reuse the year variable in various other attributes integrating ...
- 899
2
votes
0
answers
44
views
Use psycopg in pass parameter of type geography(MultiPolygon, 4326)[] to PostgreSQL function
I am using PostgreSQL + PostGIS to save a country border.
Following is the table definition:
CREATE TABLE IF NOT EXISTS country (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(...
- 53
0
votes
1
answer
45
views
Why .ToString, Or .STAsText() keeps returning geographies instead of strings
The question underlines the specific issue, but here's some context.
I'm trying to get the closest distance to each point between a set of points and polygons in SQL. The points are just lat/long of ...
- 31
3
votes
1
answer
41
views
Get spatial endpoints grouped by attribute via virtual layer
I have a set of lines consisting of several single object LineStrings.
These line can be collected to routes by attribute, e.g. a street consisting of multiple lines from crossing to crossing, but ...
- 659
4
votes
1
answer
141
views
Joining within a table with ST_Intersects in PostGIS
I have a table with the below structure:
geom
name
ownership
01031...
foo
landlord
01032...
foo
landlord
01033...
baz
landlord
01034...
bar
tenant
01035...
bar
tenant
01036...
bar
tenant
...
- 143
2
votes
1
answer
49
views
Adding a new column with value in a virtual layer query
I've created start and end points from a LineString layer using this answer from another question:
select id, sts as route_id, st_endpoint(geometry) as geometry from pointclasstest
union
select id, ...
- 659
1
vote
1
answer
58
views
Distance unit in KNN using cross join lateral
The underlying geometries are SRID 4326. I want to confirm whether "pma.dist" in the select clause would be a distance in meters. Here is my code:
SELECT
clusters.areaid,
...
- 839
1
vote
0
answers
118
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 ...
- 53
1
vote
1
answer
95
views
Create e virtual layer (points to line) refering to specific rows in attribute table
I have a point layer "Points" with the following attributes:
Name
FromNode
ToNode
Startheight
Gradient
Length
Endheight
14870040
14870040
14870055
39
0.005
57.51
14870056
14870056
...
- 61
1
vote
0
answers
52
views
PostGIS / ST_Intersection error GEOSIntersects: TopologyException [duplicate]
With PostGIS, I want to generate a new table being the result of the intersection of a table of mixed geometries and a polygonal layer. Here is my attempt. I think this request is valid. But I have an ...
- 909
5
votes
2
answers
181
views
Ordering points when making line in QGIS Virtual Layer
I've got a QGIS point geometry layer with four point features and a numeric field sequence. I want to create a virtual layer joining these points into a line geometry layer (which will have one ...
- 62.6k
2
votes
1
answer
63
views
Create a new point geometry from origin point geometry, distance and bearing in PostgreSQL
I am trying to create a new point geometry field from this command:
UPDATE cat.datos_parcelas
SET arb_geom = ST_SetSRID(ST_Project(geom, arbdist, radians(arbrumbo)), 32631)::geometry;
-- geom: ...
- 165
2
votes
1
answer
143
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 "...
2
votes
1
answer
63
views
QGIS Virtual layer with collected (multi-part geometry)
I have a layer that will be changing so I don't want to dissolve it and would prefer to make a virtual multi-part layer if that's possible?
I can use a geometry generator to collect the features into ...
- 1,565
0
votes
0
answers
92
views
Utilizing spatial index in SQL Server
I have a table Points with shape column for points of geography type, and a spatial index on the column to improve performance..
CREATE TABLE Points(id VARCHAR(10) PRIMARY KEY, shape geography);
...
- 11
0
votes
1
answer
76
views
Cluster nearby points into a single polygon - SQL Server
I have a table with column shape of type geography where each row represents a point.
It's required to cluster nearby points (within a specified distance from each other) into clusters of type polygon ...
- 11
1
vote
1
answer
63
views
PostGIS ST_Intersects and ST_MakePoint
I have a table called constraints_layer and columns named pk_id, dataset, name, geom. Geom is polygons. Within my Leaflet map Java I have a function that triggers on mouse double click and sends an ...
- 63
1
vote
0
answers
19
views
Raster pixels to vector points with virtual layer in QGIS
I would like to create vector points for each pixel of a raster that is located in polygon, using a Virtual layer.
Details :
I have 2 layers:
P : vectors layer of polygons
R : raster with value in ...
- 14
1
vote
1
answer
90
views
Virtual layer zonal statistic of raster using polygons in QGIS
I would like to create a virtual layer that creates polygons and make some raster zonal statistics in the geometry of these polygons.
The aim is to use the polygons geometry to analyze pixel values of ...
- 14
4
votes
2
answers
194
views
Create singlepart polygons with several polygons in virtual layer QGIS
I would like to automatize the creation of several singlepart polygons when polygons (from layer Polygons) are crossed by a the same polyline (from layer polylines), and fill the gap between like ...
- 14