Geographic Information Systems

Questions tagged [sql]

Ask Question

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

1,474 questions
Newest
Active
Bountied
Unanswered
Filter by
Sorted by
Tagged with
1
vote
0answers
24 views

How to solve parse error in pgAdmin4?

I am currently trying to add Point geometries into a table. My query is the following UPDATE postal_code.postal_codes SET geom = ST_GeomFromText('POINT(' || lon || ' ' || lat || ')',4269); ...
0
votes
1answer
39 views

Problems with “Filter” and “Select with Expression” statements in QGIS

I am using QGIS 3.10 to make simple queries using the "Filter" and "Select by Expression" tools. I have a layer with cities/towns in New York State with population attributes, e.g. ...
0
votes
0answers
32 views

How can the search by layers be made? [closed]

I am trying to search for information with 8 digits of a single layer, but the QFIELD program only allows me 5 digits to search and also lists all the layers that have that information, I would like ...
1
vote
0answers
40 views

QGIS and QField show different behaviour regarding a Value Relation with Filter Expression

I added a Value Relation Widget in the Attributes Form of a shapefile in order to generate a drop-down menu that is supposed to make fieldwork more efficient. The idea is to have an optional filter ...
1
vote
0answers
14 views

Snap lines (map matched trajectories) to road network in PostGIS

Using OSRM I have map matched a series of GPS points to a custom road network. My goal is to provide insight in where but more importantly how many times the vehicle has visited each road segment in ...
0
votes
0answers
27 views

Working around ERROR: Unknown geometry type: 11 - MultiCurve

Basically I have a polygon layer and I need to create a view which extracts about 100 different columns from about 10 tables with polygon, line and point geometry, that intersect with the said polygon....
1
vote
1answer
31 views

Spatial join points and polygons in BigQuery?

I have two tables; one has points (e.g., specific addresses with one geography POINT per row) another has areas (e.g., neighbourhoods with one geography POLYGON per row). I'd like to join them, to ...
0
votes
1answer
22 views

Ogr2ogr importing table with all in text type - how to change geometry accordingly?

I've imported a large table that has a geometry column, problem is that it is in text format, how to change this to be a point geometry in 27700 CRS? I've tried SELECT UpdateGeometrySRID('table','geom'...
-1
votes
0answers
9 views

(QGIS 3.16) Trouble with an SQL output generated from an OD-Matrix [duplicate]

I recently ran an SQL output in order to compress my Origin-Destination Matrix based off fastest travel time. The resulting SQL should have reported 2478 routes, but it only computed 2458 routes. I am ...
0
votes
0answers
26 views

Why is SQL input missing features from original Origin-Destination Matrix using QGIS?

I am using QGIS 3.16. I computed an Origin Destination Matrix for my studies using a total of 2478 (x13) routes. I then computed an SQL query in order to match each start point with its nearest end ...
0
votes
0answers
34 views

Run ST_DWithin on a subquery result from a different table

I have two tables "Customers", and "Providers" in my PostgreSQL DB. They both have a 'geom' column. Now, customer Table has a column called e.g. "profession". I want to ...
0
votes
1answer
16 views

Unauthorized SQL Query Execution warning- ArcGIS map services

A security scan of an application I maintain that uses several ArcGIS 10.7.1 map services (hosted with stand alone ArcGIS Server, not Enterprise, on AWS server, with IIS as web server) produced this ...
0
votes
0answers
18 views

Display DrillHole locations in QGIS from SQL Server database source

I'm using QGIS 3.10.9, and I want to be able to display some drill hole data from my source database, which is on a SQL server. (I've been able to export the data into CSV format and display the data ...
1
vote
1answer
27 views

PostgreSQL export geometry to .sql file [closed]

I don't know how to export the geometry of tables in a .sql format from PostgreSQL database. How can I do this?
1
vote
0answers
21 views

Using spatial indexes on point to polygon intersection query in SQL Server

I have a table (tab_point) with about 5 million points and another table with a few thousand polygons (tab_polyg). I need to find what points intersect with what polygon (to establish a count of ...
0
votes
0answers
20 views

Combine SDE Versions in a SQL View

I have two versions, from same base table, with only one feature class in both that I want to combine in a database view for a presentation. Version feature classes differ both with attributes and ...
1
vote
0answers
28 views

SQL query not loading

I did a SQL query from a Geopackage with QGIS database manager. The query is correct and it returns me the expected result with it's ID and the GEOM columns. The problem becomes when I load the query ...
3
votes
0answers
70 views

Load layers from SQLite database in Model Designer

Regularly, I have to load layers from SQLite databases through Spatialite in Qgis 3.16 and perform some algorithms. Extension of these SQLite databases is not *.sqlite but another format (proprietary)....
0
votes
0answers
46 views

Strange “RuntimeError: ERROR 160195: An invalid SQL statement was used”. when trying to debug SearchCursor

I'm trying to get a SearchCursor to function in my script. According to everything I could find, this should have worked. The code reads like this: with arcpy.SearchCursor(sickTrees,'OID@') as cursor: ...
1
vote
1answer
66 views

Counting Polygon Centroids in Polygons

I have two tables in PostgreSQL 13: hexbin table with geom field (red) sites table with geom field (blue) I want to return a table with the hexbin geometries and a total count of where the sites ...
0
votes
1answer
34 views

Using SQL View as ImageMosaic Store index in GeoServer

I have an application server creating rasters from the vector data it receives periodically. The data contains either "real" measurements or forecasts about a specific weather phenomenon, ...
6
votes
1answer
99 views

PostGIS Voronoi Polygons with extend_to parameter

I am creating voronoi polygons for locations inside a country, using PostGIS. The function creates these areas for ~80,000 locations in less than a second. However, I can only get the polygons to ...
2
votes
1answer
50 views

OGR2OGR import to SQL from geometry to geography

I'm trying to import a shapefile to SQL Server 2019. This command works: ogr2ogr -f "MSSQLSpatial" "MSSQL:server=[myserver];database=test;tables=CAPZONE;UID=[myuser];PWD=[mypassword];...
0
votes
0answers
12 views

How to create a PGDUMP from LVBAG format using ogr2ogr?

I am trying to figure out how to use this GDAL driver to create a SQL dump, using the ogr2ogr commandline. I have tried the following: ogr2ogr -oo AUTOCORRECT_INVALID_DATA=YES --config PG_USE_COPY=YES ...
0
votes
1answer
43 views

Adding and populating Geometry column from an existing table

I have a PostGIS table with three columns (rid, rast, fileName), now I want to add a geometry column and populate the geometry column using the rast column. A single rast index may have multiple ...
2
votes
1answer
97 views

Removing fid from the GeoServer layer

In my PostGIS table, I do not have any column with feature ID or FID. But when I open it in GeoServer layer, It shows the FID but I don't want to show it when i click over the polygon, Is there any ...
1
vote
1answer
32 views

Labeling every polygon in shapefile except for one in ArcMap

I am using ArcMap 10.8.1. I am trying to create a map of a county which does not need to be labeled since it is for a report concerning that county, but every surrounding county does need a label for ...
0
votes
1answer
23 views

Dissolve shapefile polygons on SQL Management Studio 18

This is the first time I am trying to work with a shapefile on SQL and I am a little lost. I uploaded a shapefile with all the counties of the US and what I am trying to do is to dissolve all ...
0
votes
1answer
43 views

Data from EPSG dataset in proj format

In the EPSG dataset obtained from https://epsg.org/home.html where to find the values to fill up the field of +proj =" or +zone= " (to be in proj.4 string)from the database itself? Will the ...
1
vote
2answers
71 views

QGIS: Comparing attribute values from different layers with Expression string builder

I want to compare attribute values from different attributes from multiple layers in the Expression string builder in QGIS (3.16.1) in of these layers, but I don't know how to do that. I've tried out ...
0
votes
2answers
53 views

Heading rate/bearing from GPS points in BigQuery

I want to calculate heading rate / bearing from GPS points in Big Query. I've check all of the geography functions in BQ but found nothing helpful. Has anyone done it in BQ? My data consist of ...
1
vote
1answer
26 views

Distance between current and previous GPS Point BigQuery

I have a table with 150.000 rows containing DateTime, GpsLongitude and GpsLatitude columns. Timestamp difference between rows is 10 seconds. I want to calculate the distance between every current and ...
1
vote
1answer
53 views

Selecting lat | long | name with given “polygon(,,,,,,,,)” in PostGIS using Python

My table in postgis is this with name samplecol: vessel_hash | name_city | station | speed | latitude | longitude | course | heading | timestamp | the_geom ...
1
vote
0answers
19 views

Updating and merging geometries using a 3rd table as the connector

I'm looking for some help with the following. I have 3 tables in my Oracle Database. Table A: IDnumber Geometry 001 Geom1 002 Geom2 003 Geom3 Table B: IDnumber Fieldnumber (...
0
votes
0answers
30 views

Updating geometries using another table whose records need to merge in SQLDeveloper

I'm looking for some help with the following. I have 3 tables in my Oracle Database. Table A: IDnumber Geometry 001 Geom1 002 Geom2 003 Geom3 Table B: IDnumber Fieldnumber (...
1
vote
1answer
57 views

Removing last part of a field in QGIS attribute table, not the same length of cells

I got a question regarding removing and/or creating a new field just with the 4 first characters of a field. Whenever I search for this I just find answers about how to remove a specific number of ...
2
votes
1answer
34 views

Generating a SQL statement from a text file to make a selection

I have a text file of project names used in various workflows, it is structured as the following: Project_2020_1m_20200212_0900 Project_2020_1m_20200213_0900 I want to use the text file to create an ...
1
vote
0answers
91 views

Ogr2ogr importing shapefiles into postgresql

I have enabled postgis extension already and have imported 3 shapefiles through the qgis db manager. Problem is that now I still have 2 large files to import and leaving qgis importing the entire ...
4
votes
1answer
87 views

Join on field with multiple values

I have a field "istat" that contains multiple values ​​of the istat code (municipal code): and a table containing the centroids of the municipalities I am looking for a way to join the &...
0
votes
0answers
23 views

How to get polygons contained within another polygon, including those that touch the border in BigQuery Standard SQL?

I'm trying to get blockgroups contains within a designated market area polygon in BigQuery. I've tried using st_contains and st_covers but I still only get the completely contained ones (and not the ...
5
votes
2answers
170 views

CREATE TABLE AS with geometry in SpatiaLite

I would like to create a new SpatiaLite table based on a command. For example, I would like to transform a table to a different coordinate system: SELECT Transform(Geometry, 3068) FROM points_berlin; ...
6
votes
2answers
273 views

Snap points to the nearest location on a polygon

I have some PostGIS SQL that generates uniformly distributed point cluster centroids within polygons (loosely based on this tutorial). The number of point cluster centroids generated is based on the ...
1
vote
2answers
49 views

PostGIS: Syntax for ST_Collect into a multipolygon keeping internal boundries

I have a PostGIS table with many polygons representing islands these all form a common group How do I create a new MultiPolygon (keeping internal boundaries) and give it a new name? CREATE TABLE ...
4
votes
0answers
65 views

ogr2ogr creates too many columns with LEFT JOIN

I am creating a GeoPackage using several shapefiles using ogr2ogr (GDAL 2.2.4): ogr2ogr -f GPKG /path/to/out.gpkg -append -nln out -a_srs EPSG:4326 /path/to/in1.shp ogr2ogr -f GPKG /path/to/out.gpkg -...
0
votes
0answers
38 views

Using geography cast as geometry in QGIS No SRID in Airports table. Other table have geom and is 26986

I trying to add a layer into the map in QGIS. It will execute the SQL and show the answer but will not add it to the map. It states there a geometry error. I type cast geom::geography it should allow ...
0
votes
0answers
35 views

Query geodatabase with PostgreSQL to show - schema, table, column, domain name

How to query geodatabase system tables using PostgreSQL to result this: schema, table, column, domain name I found this, but nothing for PostgreSQL https://desktop.arcgis.com/en/arcmap/10.3/manage-...
1
vote
0answers
99 views

Join the result of an SQL query from PostgreSQL to QGIS

I want to send an SQL query from a Python script in QGIS to PostgreSQL/PostGIS and get back the result as a table which I then join in QGIS to a shapefile. How do I do that? At the moment I try to ...
0
votes
1answer
106 views

Execute loop in PostgreSQL query

How to execute for each table in the list one fixed query replacing just %table% in PostgreSQL (like variables and loops) sql = 'select * from $table$ where...some_filter' $table = {tb1, tb2, tb3...}...
1
vote
0answers
44 views

Virtual Layer from SQL query is extremely slow

I have two layers, one is a grid of circles and the other is all Census Block Groups (CBG) in the state of Florida. I created a Virtual Layer out of this code SELECT CirclesFlorida.name, group_concat(...
2
votes
1answer
61 views

Left join resulting in weird output in QGIS's Virtual Layer

I want to join two layers, a polygon layer "plan" and a point layer "education point". My goal is to join the data from "education point" layer to the "plan" ...

1
2 3 4 5
…
30