Questions tagged [sql]
Acronym for Structured Query Language. A syntax for retrieving and manipulating data from a relational database.
1,842
questions
1
vote
1
answer
750
views
GDAL og2ogr: insert/update existing PostGIS table from shapefile [closed]
I work with Windows, PostGIS and GDAL (I use gdal/ogr2ogr from the command prompt. Version of GDAL available under C:\Program Files\QGIS 3.16\bin).
Up to now, within my PostGIS database, I was ...
- 939
3
votes
1
answer
263
views
Counting all points in polygon where the name of polygon and point matches with SQL
In QGIS I have two datasets: (1) one with points that have two fields, the unique identifier "PK" and "species" - either H, L or G (2) second with polygons of areas, again with ...
- 31
2
votes
1
answer
133
views
Showing Origins on ARRAY multiple routes - pgRouting
I have routed between multiple points using pgRouting. I have aggregated the total cost of each route (length) into a new table using edge = -1 to be imported into an inverse distance weighting ...
- 161
0
votes
0
answers
250
views
SQL Query to limit loaded feature from WFS Layer to the extent of a feature in another layer
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: ...
- 1
13
votes
4
answers
2k
views
Deleting small holes in polygons specifying the size with PostGIS
I have a big MultiPolygon layer in which I want to delete holes smaller than 100m².
QGIS do it well and fast with the algorithm "native:deleteholes", but I need to use it as part of a SQL ...
- 695
1
vote
1
answer
112
views
Getting base Line of PostGIS skeleton
I'm interested in using OGR/GDAL, PostGIS, or QGIS in order to achieve the following:
Working from the PostGIS docs, how would one further simplify this line to the middle line? I've tried utilizing ...
- 1,191
2
votes
0
answers
115
views
Interpolated for every point in the raster space
I have here made a demo of my attempt turn points on a map into a with values raster then use St_interpolateraster and than send it back as geojson. It returns values and they are interpolated well. ...
- 161
6
votes
2
answers
1k
views
Creating Virtual Layer as union of multiple layers with same schema in QGIS
I have a Layer Group surveyor with lots of layers (shapefiles) with seemingly random names, and with identical schemas. I would like to combine them all to one layer; preferably with a Virtual Layer. ...
- 759
2
votes
1
answer
246
views
Improve performance of startpoint query (ST_GEOMETRY)
I have a SDE.ST_GEOMETRY query in an Oracle 18c EGDB that gets the startpoint of polylines:
select
objectid,
sde.st_geomfromwkb(sde.st_asbinary(sde.st_startpoint(sde.st_geometryn(shape,1))), ...
- 1,132
0
votes
1
answer
656
views
Select SDE.ST_GEOMETRY as JSON text (using SQL)
I have a multipart M-enabled SDE.ST_GEOMETRY polyline FC (in an Oracle 18c EGDB):
Using SQL, I want to select the geometry as JSON text:
{"hasM":true,"paths":[[[0,5,0],[10,10,11....
- 1,132
4
votes
3
answers
153
views
Points which are beyond certain distance from multiple points
I have two tables - first is a list of positions (points) and second is a list of cities (cities). I am using PostGIS and can find all points outside 10nm of New York by using the SQL statement -
...
- 163
1
vote
2
answers
700
views
Loading SQL X/Y Table into QGIS
I'm new to QGIS (moving from ESRI).
I'm wanting to use an SQL table that has X and Y coordinate columns for viewing in QGIS as a layer. I've created and tested the ODBC connection to the database ok ...
- 21
2
votes
0
answers
211
views
Possible to use output data variable from Iterator in SQL query?
I am looking to use a value returned by an iterator as part of an SQL query but cannot figure out the right way to do this with an SQL expression:
I have a table of 7 text values (Cadastre) that the '...
- 21
1
vote
1
answer
62
views
Reviewing query from which Virtual Layer was originated in QGIS
I created a Virtual Layer by going in Layer > Add Layer > Add or Edit Virtual Layer. This new layer was created by building a query on another layer I had in my project.
Ho can I check, ...
- 887
1
vote
1
answer
757
views
Creating a Point with SRID - MariaDB
I have a table containing a longitude and latitude (as doubles) in MariaDB. What I'm trying to do is create a point with a specific SRID - 4326. The current way that I create a point is by using the ...
1
vote
1
answer
381
views
Count records from a Feature Class, where the combination of 2 columns match another Table(View)
I have difficulty formulating a good title for this problem.
Problem statement
I have the following 2 datasets, that are included into the same file local GDB. For these tables I want to find the ...
- 1,156
2
votes
1
answer
438
views
ST_Within is only working on selective rows
I have two tables (Table1, 10k rows, and Table2, 100k rows). Table1 contains latitude and longitude columns while Table2 contains a geometry column (representing a polygon). I wanted to merge these ...
- 23
0
votes
1
answer
587
views
Geo search MySQL table for rows that are within proximity
I have an sql table that has some data on retail locations with lat, long coordinates. My application queries the table to pull the data using python. Here's some sample data:
table1
id name lat ...
- 473
0
votes
1
answer
80
views
Is a GIS database necessary? Most efficient way? [closed]
I have been tasked with finding an efficient way to store data of thousands of addresses to compare to a broader set of addresses of all retail stores in America. Ideally, I would like the database to ...
3
votes
2
answers
216
views
Create a new table, sorted by st_area function in descending order
I have a very large table in postgis which has more than 10 million polygons and I wanted to create a function using this table for pg_tileserv but the problem is the size of table which takes too ...
- 41
2
votes
1
answer
149
views
Virtual-Layer/SQL-View: SQL to get list of all values in a group (SpatiaLite/GeoPackage) [closed]
I have a GeoPackage-Table and it's looking like that:
id data
-- ----
1 A
1 A
1 B
2 A
2 B
3 B
3 B
I need a virtual layer or a SQL-View that should look like that:
id data
--...
- 8,262
1
vote
0
answers
50
views
SQL expression to decision tree
I have an SQL expression with a lot of CASE WHEN. I'm trying to see if there is a simple way to take this expression, and turn it into a drawing decision tree.
This would be helpful to explain for ...
- 1,659
2
votes
1
answer
85
views
MapInfo SQL Query with Metacharacters?
I have +8 million edge in MapInfo. I want to select character+digit but I can't.
I tried querying as [a-z]+[0-9] but failed to use metacharacters in MapInfo.
"New York 1. Street" is true.
&...
- 53
1
vote
1
answer
529
views
Variables in SQL expression in ArcPy script
I have a Python script with three parameters to collect user inputs via GetParameterAsText...
Buffer_Distance = arcpy.GetParameterAsText(0) # a string data type number (e.g. 40)
slp_min = arcpy....
- 43
0
votes
0
answers
55
views
Getting nearest city to point [duplicate]
UPDATE: This question was entered, but by mistake I re-asked in another post, so please consider this post as the one that answer this. This question should be closed and mark as duplicate.
I have a ...
- 229
2
votes
1
answer
194
views
Getting nearest city related to point in PostGIS
I have a table clusters with points.
CREATE TABLE public.clusters (
cluster_id int4 NULL,
punto public.geometry NULL,
cnt int8 NULL
);
INSERT INTO clusters ...
- 229
1
vote
0
answers
44
views
How do I project and merge MS SQL views into a feature class that can be published as a service?
I have three views (or enterprise geodatabase tables) in a SQL database. They each contain partial components of a complete dataset, but have distinct coordinate systems. I want to project and combine ...
- 11
0
votes
1
answer
1k
views
Intersect points with polygons using SQL Server query from ArcGIS Enterprise Geodatabase
I have 2 databases in ArcGIS Enterprise Geodatabase in SQL Server. One database (DB_A) contains a feature class with points and the other database (DB_B) contains a feature class with polygons. I'd ...
- 271
2
votes
1
answer
5k
views
Getting "No function matches the given name and argument types" when converting points to Polygons / Voronoi Diagram
I have these points
stored in my database. Now I am trying to use to create a Voronoi diagram with ST_VoronoiPolygons(). I have tried doing:
SELECT
(ST_DUMP(ST_VoronoiPolygons(ST_Collect(geog)))...
- 173
0
votes
0
answers
183
views
SQL query on array data in ArcGIS attribute table
I have an attribute table where courses offered by different colleges are in an array, I am trying to Query using ArcGIS query builder so as to return colleges offering a specific course within the ...
- 9
4
votes
1
answer
147
views
Grouping lines by name and determining their proportions using Virtual Layer
I have a line layer with hiking trails. The hiking trails are divided into their surfaces. So one hiking trail consist of parts with different surfaces.
I would like to use a virtual layer to group ...
- 8,262
2
votes
1
answer
527
views
QGIS: importing dataset based on zipcodes, not coordinates
for a current project I want to connect nation-wide PV-modules (photovoltaic modules) to power grid nodes via zip codes and their respective power demands. For this reason, I downloaded QGIS as I was ...
- 51
-1
votes
1
answer
457
views
SQL statement for count of different ranges [closed]
In QGIS, I want to make a SQL-statement which should return the counts of different year ranges e.g. in column 1 should be the total count for distinct values for all years, in column 2 1950-1990, in ...
- 931
0
votes
0
answers
110
views
Spatially Joining two tables
I have two tables in MariaDB. I am trying to join these two tables (Table 1 and Table 2). Table 1 contains Longitude and Latitude columns and Table 2 contains a MultiPolygonGeom column, which contains ...
- 23
0
votes
1
answer
2k
views
Converting a WKT column to type Geom
I have the following table in my MariaDB database:
This table contains a column called 'coordinates' (type: LONGTEXT) with a wkt representation of either a MultiPolygon or a Polygon. I am attempting ...
4
votes
2
answers
150
views
How to specify the output of the previous algorithm as input to the QGIS "Execute SQL"?
I am trying to use the SQL Execute in the graph modeler, receiving as input the output of another algorithm. The problem is that I couldn't specify that the "input" in the FROM of the SQL ...
- 49
1
vote
1
answer
458
views
Representing a route that doubles back on itself in BigQuery with a LineString
I'm trying to represent routes in BigQuery, but running into issues for routes that double back on themselves. Consider the following LineString that proceeds from the southwest to the northeast of ...
- 133
1
vote
0
answers
161
views
QGIS virtual layer SQL Query Arrays
I have a polygon layer (A) with a field containing the farmers that use that polygon (the UUID of each farmer):
In each polygon feature can work from one to several farmers as it can be seen in the ...
- 53
1
vote
1
answer
314
views
QGIS Virtual Layer SQL Query sum if
In QGIS I have a polygon virtual layer (A) with 3 fields:
"Codes": Codes of farmers. Each farmer can be duplicated up to 5 times based on next field.
"Intensity": Farming ...
- 53
4
votes
2
answers
229
views
Deleting entries (duplicates) from a GPKG using OGR
I tried to delete duplicates from a GPKG file using ogr2ogr and a SQLite statement from SO:
ogr2ogr -sql "DELETE FROM layer WHERE rowid NOT IN (SELECT MIN(rowid) FROM layer GROUP BY ...
- 3,731
2
votes
2
answers
142
views
QGIS ST_Union polygon and table
In QGIS I have:
A polygon table (A) with farmed area. It has an UUID field.
A no geometry table (B) with farmers data. It has a UUID field.
A no geometry pivot table (C) that relates them both N-M, ...
- 53
2
votes
1
answer
1k
views
Map not showing behind polygon
I have inserted polygon data in POSTGIS using an SQL query via dbeaver. However, when I view the value tab, only the polygon is showing without the map whereas when I am seeing Multipolygon in the ...
- 47
1
vote
0
answers
75
views
ArcGIS: restrict FeatureLayer SQL access based on token
I am working on a web app using the ArcGIS API for JavaScript that fetches a FeatureLayer from ArcGIS Online. The web app has users with many different permission roles and I want users to be able to ...
- 11
2
votes
0
answers
434
views
Use spatial query to load WFS data into QGIS
I tried to load a WFS into my QGIS project but the number and complexity of the polygon features are to high to handle for QGIS so that it throws an error. So I tried to add a spatial query to the ...
- 31
0
votes
1
answer
329
views
Converting SQL queries from PostGIS to BigQuery
I have a table called drawn_items with columns “the_geom”, “fsrn_db” , & “area” in Carto2 which is queried by SQL. Having recreated the table in Carto 3, in order to use the query, I must ...
- 63
7
votes
1
answer
1k
views
Actual PostGIS query execution time MUCH slower than expected
I've run into a performance problem from which I'm not sure how to move forward. I have a table with ~150M rows. And I have a query that in EXPLAIN has a cost of just cost=0.55..209.36, but in EXPLAIN ...
- 111
0
votes
1
answer
290
views
Clip a shapefile by a line border
I'm trying to generate a clipped file of a sample polygon geography (in this case, zip) and the U.S. coastline.
This is the query I currently have:
select
state_code,
zip_code,
st_union(st_dump(...
2
votes
0
answers
119
views
Memory efficient way of creating a large PostGIS table
I am trying to add the results of a query to PostGIS DB. My query converts a raster band into polygons. Although the original raster is quite big (~150GB, 12 bands) my below query runs reasonably fast ...
- 469
0
votes
1
answer
415
views
Query SQL x Virtual Layer x Joined Fields
I have a spatial layer which I did a JOIN tool with a data table. Now in my attributes window I see all the fields: the original ones and the ones from the joined table.
Both are in a PostgreSQL ...
0
votes
1
answer
97
views
Use SQL on Layer in QGIS/SQL console in QGIS
There is the Field Calculator which has a lot of functionalities. Then there is Select by Expression which has a SQL Style Syntax.
However, is it possible to write plain SQL in QGIS on a Layer like a ...
- 3,378