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
1
vote
1answer
78 views

Selecting polygon feature classes with one record in Oracle geodatabase using SQL query?

I have an Oracle Enterprise geodatabase with many feature classes. I would like a SQL query to find all the polygon feature classes with only one record in its table. So far, I can only query all the ...
1
vote
1answer
434 views

Splitting polygon into separate polygons using table of individual lines

I have been trying for a while but have not been able to split a polygon at multiple locations, using a table of lines, to get what is on the right side of the above image. The best I can achieve is ...
1
vote
0answers
23 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
0answers
32 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. ...
3
votes
2answers
127 views

Delete duplicate geometries from ORACLE table

I have searched a while now, but there seems to be no answer including an example on how to find and delete duplicate geometries from an Oracle DB table. I am facing a situation where I have a table ...
1
vote
0answers
39 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 ...
0
votes
0answers
31 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
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 ...
2
votes
1answer
149 views

How to get the intermediate points between a route origin and destination?

I use Redshift and recently Amazon Redshift has updated the database to be able to use some spatial functions. https://docs.aws.amazon.com/redshift/latest/dg/geospatial-functions.html In a table I ...
20
votes
5answers
17k views

Getting list of distinct values from shapefile field using QGIS?

I have a shapefile whose features I can filter by specifying a where clause in the query dialogue. F.x. I have a field named 'city_name' and by stating 'city_name = "London"' in the where clause only ...
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....
45
votes
9answers
19k views

Selecting features that do NOT intersect in PostGIS

Using PostGIS, I just want to select points that fall outside of polygons. Ultimately this is the inverse of the ST_Intersects, as far as I can see it. Example: I have a taxlot layer and a address ...
1
vote
1answer
30 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
3k views

How to find Nearest point on line from a point cordinate in SQL server

I want to find out the nearest point on my road network which is a multiline string from a point with lat lon value. The below code I have tried for finding buffer distance line from a point. ...
0
votes
1answer
20 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
vote
1answer
104 views

Measuring area covered by linestring

I have a trajectory given by GPS which I store as a ST_LineString() and I'd like to know the area covered by the line string. I know the "width" of the trajectory, but I'm not sure how to approach the ...
-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
3answers
137 views

Is it possible to dynamically construct the SQL query where clause in ArcGIS layer based on the URL parameters?

I have an ArcGIS layer published to my local server at: https://baseURL/myMapServer/0 I want to dynamically construct the query where clause for this layer based on the URL. For example: https://...
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
2answers
52 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 ...
0
votes
2answers
137 views

Clarification on Oracle sdo_gtype

I need some clarification on oracle sdo_gtype 4402. I understand that sdo_gtype is codded as DLTT. D: Dimension L : linear reference T T: TT identifies the geometry type. It's clear to me that first 4 ...
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
1answer
47 views

Stop SQL Server selecting outside buffer

I am trying to select all the parks within 200m or a railway. I can easily create and visualise the 200m buffer. However when i try and find only the parks that intersect it i get all parks returned,...
-2
votes
1answer
239 views

Writing rows from file geodatabase Table to NonGDB Table in SQL Server using Python [closed]

I have data in a file geodatabase table that I want to write into an existing table in SQL Server (not enterprise-enabled) using Python. I am already using pyodbc to read SQL data elsewhere, but am ...
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, ...
1
vote
1answer
141 views

pgr_alphashape returns a self intersecting polygon

After using the function pgr_drivingdistance() I am attempting to make a polygon with pgr_alphashape. Following the example found in the documentation I should do the following: https://docs.pgrouting....
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?
5
votes
2answers
169 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; ...
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 ...
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 ...
7
votes
2answers
988 views

Get ArcGIS to recognize ST_POINT

I have a SDE.ST_GEOMETRY line feature class. I've created a view on it that returns an st_point column. SELECT OBJECTID, SDE.ST_STARTPOINT(SHAPE) AS START_POINT FROM ENG.ROAD There is a known Oracle ...
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 ...
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
1answer
65 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
59 views

SQL wildcard for Field Name?

I'm wondering if there's anyway to use a wildcard character in a field name. Like using ["Species_%" = 'Hemlock'] instead of ["Species_1" = 'Hemlock' OR "Species_2" = 'Hemlock' ...]. I tried the way ...
0
votes
0answers
45 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: ...
2
votes
2answers
2k views

Multiple markers on Google Maps using JavaScript, PHP and SQL

I am trying to query the MySQL database that has a table with firstname, longitude, latitude in it. I want to get the longitude and latitude and display them as multiple markers with the firstname as ...
1
vote
1answer
75 views

Creating a Bounding box on pgr_Dijkstra to make it faster

I have a problem with the Dijkstra algorithm in regards to the query speed. My current php script for the query is looking like this: $sql = "SELECT * FROM pgr_dijkstra( 'SELECT id, ...
0
votes
1answer
116 views

Alternate of intersects ?# operator in postgres

Is there any way to find if a path is intersecting with a box in postgres without using ?# or using an alternative? I have this query below and hibernate's EntityManager is throwing an exception ...
0
votes
1answer
42 views

Using features of CARTO Builder to dynamically calculate the average of selected columns

I am trying to find a simple way to calculate the average of selected columns (avg per row) dynamically in CARTO, using only the pre implemented features of CARTO Builder so that it is as user-...
3
votes
2answers
617 views

Combining ST Buffer and Intersect with Where clause

I'm trying to create a buffer around a single point in a spatial entity and finding all the points from a different entity that fall within that buffer. I tried two approaches. The code below runs ...
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 ...
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];...
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 ...
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 ...
1
vote
1answer
419 views

How to remove artifacts with SQL Server GEOMETRY::UnionAggregate()

We'd like to do an effective "real-time dissolve" on an Esri feature class with a SQL GEOMETRY shape where a parcel layer is "dissolved" into products using an attribute (ProductID) of the parcel ...
2
votes
1answer
96 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
2 3 4 5
30