Questions tagged [sql]
Acronym for Structured Query Language. A syntax for retrieving and manipulating data from a relational database.
1,836
questions
0
votes
0
answers
189
views
Enterprise geodatabase maintenance Esri vs. SQL Server (rebuild index and update statistics)
I'm looking at two geodatabase maintenance practices and seeing if they need to be updated/refined, and I'm noticing that these can be accomplished via ESRI tools or directly via SQL / within SQL ...
0
votes
0
answers
182
views
QGIS format for 'Execute SQL query' in modeler
I need to run the equivalent of the following from MapInfo SQL in the QGIS Execute SQL tool within the modeler.
Select * from Road_Reserves, DSC_Roads where DSC_Roads.Control="Private" and ...
0
votes
1
answer
35
views
ArcGIS Desktop with Database Connections: Can not display coordinates from a view if aliases are used
The view Locations_View created in Microsoft SQL Server Management Studio is accessible in an ArcGIS Desktop project by Database Connections. Its numerical fields PT_XCOOR and PT_YCOOR are usable in ...
0
votes
1
answer
147
views
How to interpolate a point between two points in Oracle
I have an Oracle database table which stores points of a track of moving objects.
object_id
geometry
timestamp
1
...
1970-01-01T00:01:00
1
...
1970-01-01T00:02:00
1
...
1970-01-01T00:03:00
2
...
...
1
vote
2
answers
270
views
PostGIS - Create view (or materialised view) of existing polygons reprojected to Lat/Long (4326)
I'm fairly new to SQL and have a set of existing PostGIS layers in our country coordinate system (EPSG: 2193).
I would like to create a view (or materialised view...but initially a simple view until I ...
3
votes
2
answers
377
views
QGIS: Select feature using SQL with highest value for attribute, grouped by another attribute
I have a relatively small attribute table that includes a column where data (parameter 1) is identical across several rows. I would like to select only the row with the highest parameter 2 based on ...
2
votes
1
answer
475
views
Using Select by Attributes for large list of values in ArcPy
I have a CSV of integers containing meter numbers varying from 9 digits to 11 digits. I've written a simple script to read CSV to a list.
import pandas as pd
path = "example_path.csv"
df = ...
1
vote
1
answer
340
views
FME: SQLExecutor [PostGIS Format] ST_Intersects() Not Recognized
Scenario:
[A] 5,784 polygon geometries + [B] 2,100,000 address points
Source Format: [A] GDB Feature Class, [B] PostGIS Table
Goal:
Find which address points [B] intersect with polygon geometries [A]
...
5
votes
2
answers
4k
views
Can you use SQL in QGIS?
Is there a way to use SQL on a shapefile dataset in QGIS? I am used to using SQL to analyze my data.
0
votes
1
answer
19
views
Selecting features within joined attribute table that show intersection between street type and range of average traffic flows using ArcMap
I am trying to do a data analysis of traffic streets that would show the amount of Average Daily Traffic (ADT) as it corresponds to streets with 2 lanes. I have a feature class that shows ADT and ...
0
votes
0
answers
102
views
R SQL importing Polygon errors System.FormatException: 24119
Any advice here from you all.
I am converting a .kml file in R and uploading the data to SQL.
I am pulling data from here: https://www2.census.gov/geo/tiger/GENZ2021/kml/cb_2021_08_tract_500k.zip
...
2
votes
0
answers
176
views
Improving performance of spatial intersection and aggregation query
Edit:
Per a comment, here is some more context and the basics of what I'm looking to do, that I think would better enable someone to offer input from a first principles perspective. Its a lot of ...
1
vote
1
answer
81
views
The function with WITH is throwing an error in PostgreSQL
I am trying to put some statements into the following function.
CREATE OR REPLACE FUNCTION public.raster_data_extraction(
resolution integer,
pixel_size double precision,
upper_x double ...
0
votes
1
answer
140
views
R paste polygon coordinates for SQL
Everyone,
I am new to this GIS data and SQL, but have pretty decent R knowledge.
I am looking to extract the Polygon coordinates from a list so that I can paste into a SQL input query.
I am pulling ...
1
vote
0
answers
68
views
Area of two intersecting polygons on PostGIS
I am trying to calculate the area of two intersecting polygons using PostGIS:
I've found this solutin:
SELECT layer.text,
layer_2.text,
sum(st_area(st_Intersection(layer.shape, layer_2....
0
votes
0
answers
102
views
ArcGIS Oracle bulk update using spatial join
I am trying make bulk update the table using a spatial join which has millions of records,
updating the ID from Parcels layer to the Park (Points) layer using Oracle SQL query, I used geometry ...
1
vote
1
answer
576
views
How do I select features with the same values but different IDs from a layer's attribute table in QGIS?
I have a multipolygon layer with several regions (green circles) that are identified through numerical fids. Within each region are multiple points (orange dots) with individual pointIDs. See example ...
4
votes
1
answer
80
views
Polygon numbering using virtual layer and polyline in QGIS
I would like to number each polygon that is crossed by a polyline.
For every polyline entity, the numbering has to be reset from 1.
I would like to keep the same logic of numbering incrementation used ...
1
vote
1
answer
276
views
Correct syntax for filtering of geopackages (SQL)
I already asked about this topic, but I am still struggling.
I created a geopackage with railways of Germany. I need to filter some sections out. I understand that geopackages work with SQL syntax for ...
0
votes
1
answer
209
views
Updating a large PostgreSQL table using QGIS [closed]
I have a large table on a (non-spatial) PostgreSQL database which I had created using QGIS to upload the (geometryless) attribute data from a point layer. The database is merely used for data analysis ...
1
vote
1
answer
68
views
How to isolate overlapping polygons in the same layer with SQL
I have one layer with hundreds of polygons and I would like to attain a list of overlapping feature_ids. I read that I need to join the table to itself but I don't understand how that helps.
This is ...
2
votes
1
answer
196
views
QGIS - Merging two tables and make lines
I need help with merging two table and create geometry.
I have two points layer with features tables and try create polyline layer.
When I create virtual layer with next querry then result is polyline ...
4
votes
1
answer
289
views
Locate ways (multilinestring) that intersect points
I have two tables: crimes (points/ST_MakePoint) and ways (multilinestring). Initially what I did was reposition the points to the point closest to the track using the following:
UPDATE renzagoras....
1
vote
1
answer
90
views
varchar to numeric in PostgreSQL [closed]
I have a shell script in Linux that downloads data from a WFS and writes it into a table in PostgreSQL.
The workflow:
Initiate by cronjob
Download data from WFS and stored locally as shapefile
Table ...
1
vote
1
answer
142
views
Inserting data and point into a table PostGIS / Error: parse
I'm trying to import in a table with columns (name, x, y, geom) like this:
INSERT INTO public.table (name, x, y, geom)
VALUES ('name', X, Y, ST_GEOMFROMTEXT('POINT(X,Y)', SRC))
But I'm obtaining this ...
1
vote
2
answers
541
views
Create dynamic text in legend for visible Layer on map layout using SQL expression
QGIS 3.16
Looking to insert an expression to my layer, Survey Coverage (text), in the legend. I want dynamic text to reflect the attribute value from the field, VIS_RADIUS (Integer64) so it looks ...
0
votes
0
answers
95
views
OGR SQL Empty Output
I have a Powershell script that loops through a database and outputs the spatial tables into another database in GDA2020 datum. I see one particular table is outputting but is empty. This table should ...
2
votes
1
answer
62
views
Duplicated attributes problem while adding attributes of points to attribute table of lines in QGIS
I am trying to add the id numbers of my points to the attribute table of my lines.
I used this post [https://gis.stackexchange.com/a/355320], which solves the problem. However, the resultant layer has ...
1
vote
0
answers
104
views
ogr2ogr's sql works with small number of inputs to filter geopackage, but doesn't with large input
I have a .txt file that has all the features I want to select. It looks like this in the small version:
('100mN25991E46719','100mN25993E46718','100mN26000E46712','100mN26002E46691','100mN26003E46700','...
5
votes
3
answers
709
views
Get the first 100 points ordered by distance in PostGIS
I have a table of clients where I have the location of the client. The table has the latitude and longitude column and the geom field that creates it from latitude and longitude with the following ...
1
vote
1
answer
482
views
Extract SDO_GEOMETRY line elements using index position / dot notation
SDO_GEOMETRY; Oracle 18c:
Is there a way to extract elements from an SDO_GEOMETRY line using an index position / dot-notation in a SQL query?
For example, extract the X coordinate of the second vertex ...
2
votes
1
answer
239
views
SQL select the two values that bounds an input value
I have a table with date fields. I need to select two rows where the first row is the date just before an input date and the second row is the date just after the input date.
For example, given an ...
1
vote
1
answer
554
views
ST_Union in Apache Sedona
I'm trying to merge some polygons with ST_Union using Apache Sedona on a databricks cluster.
%sql
SELECT Polygon_Name
,ST_Union(Polygon_Boundary) AS Polygon2_Boundary
FROM tbl_polygon
GROUP BY ...
1
vote
1
answer
460
views
Select domain codes/descriptions using XMLTABLE instead of EXTRACTVALUE
Oracle 18c 10.7.1 GDB:
The ArcGIS system table docs have an example for extracting domain codes and descriptions from XML:
Example: Resolving domain codes to description values using SQL
--simplified:...
0
votes
1
answer
69
views
Where is the SDE.SDEXMLTOTEXT() function located?
Oracle 18c 10.7.1 EGDB:
In SDE.GDB_ITEMS_VW, there is a function being used called SDE.SDEXMLTOTEXT():
sde.sdexmltotext(d1.xml_doc) AS definition,
sde.sdexmltotext(d2.xml_doc) AS documentation,
sde....
4
votes
2
answers
506
views
PostGIS Maximum Inscribed Circle
I'm attempting to utilize the PostGIS function ST_MaximumInscribedCircle() but having some trouble interpreting the example. How can I feed the geometry into a from clause to retrieve the center and ...
0
votes
1
answer
471
views
ArcGIS Pro Definition Query between two tables
Seems like I was able to do this easily a few months ago using ArcGIS Pro 2.9. I have a database table and a feature class. I need to be able to draw in a polygon for the feature class and use the ...
4
votes
1
answer
271
views
PostGIS Nearest neighbor really slow
I'm trying to set a link between two tables (db1 and db2) with a nearest neighbor approach and some condition checking.
SELECT id, label_shop, linked_rows.*
FROM shopitem.shopitem_database shop
LEFT ...
0
votes
0
answers
1k
views
SQL query into a Google Apps Script (*.gs)
I am beginner in .gs and javascript (.js) code and I want to write a Google Apps script including an SQL query to apply it to one or more Google Sheets tabs :
tab where I write my *.gs code : ...
4
votes
1
answer
144
views
Pass a variable parameter to pgr_dijkstra (pgRouting) Edges SQL
I need to pass a parameter to Edges SQL in a pgRouting function, for example pgr_dijkstra.
I try something like:
PREPARE test (int) AS
INSERT INTO elab.test_f
SELECT * FROM pgr_dijkstra(
'SELECT id,...
2
votes
1
answer
346
views
PostGIS st_intersection returns only the lines that are inside the polygon
I have two polygons called 1, 2.
I have a linestring that crosses the two polygons but does not remain only inside them.
What I'm trying to do:
Find the linestring that is from A to D in the case of ...
3
votes
2
answers
227
views
Convert linestringz to linestringm using alter column?
I am attempting to import linestring data with or without m values, then test whether the data has m values, then add m values to the geometry if they are missing. So far, I have working tests for ...
0
votes
1
answer
73
views
Trying to query by date attribute but getting incorrect error
I am using ArcMap 10.8.1 to query features that were edited last Thursday and beyond. Why am I getting this error message about the month not being valid?
There was an error executing the query.
...
1
vote
0
answers
17
views
Connect QGIS to a database in MS SQL server [duplicate]
I have to link QGIS to MS server using my credentials. Everything seems to go well (I have access to the server and I get the list of all the databases inside) but when comes the time to access to one ...
1
vote
1
answer
719
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 ...
3
votes
1
answer
260
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 ...
2
votes
1
answer
131
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 ...
0
votes
0
answers
239
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: ...
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 ...
1
vote
1
answer
109
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 ...