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
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 ...
Lars Anders Myklebust's user avatar
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 ...
user234424's user avatar
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 ...
Denilson's user avatar
  • 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 ...
fed's user avatar
  • 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 ...
GIStrees's user avatar
  • 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="...
Anna-Lena Müller's user avatar
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", ...
Anna-Lena Müller's user avatar
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 ...
Anna-Lena Müller's user avatar
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 ...
Giene's user avatar
  • 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 &...
nat's user avatar
  • 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) ...
RebiF's user avatar
  • 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 ...
Giene's user avatar
  • 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 "...
robert tuw's user avatar
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 ...
Kurt Cederholm's user avatar
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....
fcka's user avatar
  • 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" "...
NvBgm's user avatar
  • 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 ...
Bernard Stokes's user avatar
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'. ...
Isaac Braham's user avatar
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....
MtoK's user avatar
  • 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 ...
Remi79's user avatar
  • 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 ...
Laurens's user avatar
  • 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....
TheRealJimShady's user avatar
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 ...
sMartines's user avatar
  • 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 ...
Tom Brennan's user avatar
  • 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/...
Sam's user avatar
  • 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 ...
Lucas's user avatar
  • 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 ...
EranGeo's user avatar
  • 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 ...
Paul's user avatar
  • 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 - ...
Cookie's user avatar
  • 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 ...
Yndigo Dream's user avatar
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 ...
fcka's user avatar
  • 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(...
timewalker's user avatar
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 ...
HenryClay's user avatar
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 ...
e-shirt's user avatar
  • 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 ...
Tom Harvey's user avatar
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, ...
e-shirt's user avatar
  • 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, ...
analyst92's user avatar
  • 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 ...
dot314's user avatar
  • 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 ...
MtoK's user avatar
  • 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 ...
user35117's user avatar
  • 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 ...
Spacedman's user avatar
  • 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: ...
Isa's user avatar
  • 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 "...
David Roodman's user avatar
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 ...
Sethinacan's user avatar
  • 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); ...
Sam's user avatar
  • 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 ...
Sam's user avatar
  • 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 ...
williamh's user avatar
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 ...
zlikotp's user avatar
  • 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 ...
zlikotp's user avatar
  • 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 ...
zlikotp's user avatar
  • 14

1
2 3 4 5
37