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
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 ...
Peter Bishop's user avatar
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 ...
GeorgeC's user avatar
  • 8,206
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 ...
stonebe's user avatar
  • 445
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 ... ...
chko's user avatar
  • 21
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 ...
Tim's user avatar
  • 390
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 ...
Tengger's user avatar
  • 39
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 = ...
cb101's user avatar
  • 23
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] ...
Rose's user avatar
  • 315
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.
Chace Carpenter's user avatar
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 ...
Lily's user avatar
  • 3
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 ...
BB.squared's user avatar
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 ...
mtreg's user avatar
  • 292
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 ...
Manap Shymyr's user avatar
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 ...
BB.squared's user avatar
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....
adamekcerv's user avatar
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 ...
Haris GIS's user avatar
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 ...
user avatar
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 ...
zlikotp's user avatar
  • 24
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 ...
BAE_23's user avatar
  • 611
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 ...
Tobias's user avatar
  • 111
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 ...
danaburtono's user avatar
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 ...
Grumgog's user avatar
  • 31
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....
Luis Madueño's user avatar
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 ...
CaptainAhab's user avatar
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 ...
M DM's user avatar
  • 11
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 ...
ArchMarmot's user avatar
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 ...
Josh's user avatar
  • 323
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 ...
mdgcrks's user avatar
  • 53
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','...
Lenn's user avatar
  • 1,233
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 ...
Javier Ferreira's user avatar
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 ...
User1974's user avatar
  • 1,122
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 ...
Ahmad Raji's user avatar
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 ...
James Lockyer's user avatar
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:...
User1974's user avatar
  • 1,122
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....
User1974's user avatar
  • 1,122
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 ...
Matt's user avatar
  • 1,181
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 ...
geoJshaun's user avatar
  • 1,535
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 ...
Bil11's user avatar
  • 81
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 : ...
Philippe SCHITTER's user avatar
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,...
franco_b's user avatar
  • 522
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 ...
CoderCoder42's user avatar
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 ...
jbalk's user avatar
  • 7,505
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. ...
Regulus's user avatar
  • 33
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 ...
J.Claude's user avatar
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 ...
user35117's user avatar
  • 939
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 ...
sdal's user avatar
  • 31
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 ...
Kathryn Adams's user avatar
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: ...
m_rub's user avatar
  • 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 ...
Cupain's user avatar
  • 695
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 ...
Matt's user avatar
  • 1,181

1 2 3
4
5
37