Questions tagged [sql]
Acronym for Structured Query Language. A syntax for retrieving and manipulating data from a relational database.
1,301
questions
0
votes
1answer
25 views
QGIS Select by Expression Parser Errors
My goal is to select all of the features in a layer with certain names.
I have a list of 86 Subdivision names and a I am using a county Subdivision dataset, the names in the dataset include the ...
0
votes
0answers
16 views
ogr2ogr MSSQLSpatial ERROR 1: Got Multipolygon geometry as polygonMember instead of MultiPolygon
I'm very new to using ogr2ogr so I hope I've provided enough detail to my problem. It's worked in the past.
I'm trying to retrieve GIS data from a WFS feed and saving it into SQL using the following ...
0
votes
0answers
31 views
Most equal groups by quantity field [closed]
Having a table of buildings with a flats quantity field, need to create groups of buildings that approximate as much to 150 flats. Groups have to be as much spatial compact as possible.
Any idea? ...
0
votes
0answers
15 views
Select list of tables and feature classes that have subtypes?
I have tables and feature classes in an Oracle 12c geodatabase (10.7.1).
I would like to generate a list of tables and feature classes that have subtypes defined in them.
Is there a way to do this ...
1
vote
0answers
20 views
How to filter date values using OGR 2.4.0?
I am trying to replicate a working Oracle SQL query into ogr2ogr, but having issues with a date field where clause.
My query is:
select BLOCK_STATUS_DATE, GEOMETRY
from WHSE_FOREST_TENURE....
2
votes
1answer
29 views
Updating GeoPackage records with R
I'd like to update records of a GeoPackage containing vector information in R. I've tried this:
library(sf)
library(RSQLite)
nc <- st_read(system.file("shape/nc.shp", package="sf"))
# Write Geo ...
1
vote
0answers
23 views
PostGIS - suppress errors
Is there any way in SQL to supress errors in PostGIS functions, for instance if some of your geometries are invalid but you want to report a null value for the output or something?
To give an example,...
3
votes
2answers
68 views
PostGIS don't automatically convert WKB to GeoJSON
I discovered that somewhere in the upgrades from PostgreSQL 9.5 and PostGIS 2.2 to PostgreSQL 12.1 and PostGIS 3.0 the behavior for transforming a row into JSON changed.
I am using the following ...
1
vote
0answers
42 views
CREATE TABLE AS with SELECT clause throws error in QGIS DB Manager
I am not able to run the CREATE TABLE AS command in QGIS DB Manager (Win 10, QGIS 3.4.5-Madeira. I am getting the same error in QGIS 2.18 (Win 8).
Query execution error on CREATE TEMP VIEW _tview AS ...
0
votes
0answers
30 views
Using Calculate Field in ArcGIS Pro
I have several field in an attribute table that have value, I am wanting to using the 'Calculate Field' GP tool to select the values and replace them with zero.
Anyone have any ideas on the Python ...
0
votes
1answer
37 views
Using geometry stored in SQL Server table instead of WKT as input to function
I am new to SQL Server,
I have a function called STVectorize that takes a geometry and breaks it down into its constituent vectors:
SELECT e.[id], e.[element_id], e.[subelement_id], e.[vector_id],
...
3
votes
2answers
55 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
40 views
Proper aliasing of spatial columns in SQL [closed]
Referencing the alias length_road in the WHERE clause does not seem to be allowed. Does this query as written cause the ST_LENGTH operation to be run more than once per record, and if so, how can that ...
0
votes
0answers
24 views
Accessing ArcSDE delta tables through Python?
I would like to access delta tables through ArcPy for a QC script that I am improving. Read-only. How could I output a Python dictionary that contains all the rows that were added/deleted/modified ...
1
vote
1answer
23 views
Creating a table view using SQL?
I'm trying to create a table view using SQL to join a table and a feature class together. When I try to create the view, I get an error saying there is an incorrect syntax near the keyword 'CREATE'. I'...
2
votes
0answers
30 views
How to load .geojson file w/ properties and polygons into SQL table?
I have an Azure SQL table:
-- Create the table in the specified schema
CREATE TABLE dbo.geoTest_DEV
(
fAssetId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
fAssetGeoId [NVARCHAR] (50),
...
1
vote
1answer
12 views
Extract subtype codes and descriptions in a view
I have an Oracle 10.7.1 geodatabase that has tables and feature classes with subtypes.
How can I extract the codes and descriptions from the subtypes in a view?
1
vote
0answers
19 views
Selecting by location and attibutes simultaneously? [closed]
If I have a feature class ( say 'Cities') and I would like to select all features from another layer ('Incorporated Places') that nest within the cities layer and also have the same value for their '...
2
votes
1answer
47 views
Why do two tables with valid geometry return self-intersection errors when intersected?
I have two tables A and B, both have valid geometries when I check with ST_IsValid. In my query, I intersect the two and the query stops in the middle because of this error:
ERROR: ...
1
vote
1answer
34 views
Count subset of points in polygons
Being an SQL-noob, I found a working solution to generate a virtual layer with counts of points in the polygons of another layer, inspired by this
https://gis.stackexchange.com/a/54680
SELECT ...
2
votes
2answers
41 views
QGIS 3.4 feature filter MAX(), GROUP BY does not work
I thought my problem was solved with the question I asked bevor (QGIS 3.4 feature filter maximum() does not work). But there is still a problem.
I still want to filter a layer with the maximum value ...
8
votes
1answer
126 views
Spatialite spatial queries do not function on GeoPackage
I have downloaded some data (as GeoPackages) from this excellent website: https://data.mfe.govt.nz/layer/52757-petroleum-wells/ and https://data.mfe.govt.nz/layer/52675-location-and-extent-of-nzs-...
3
votes
2answers
97 views
Calculating azimuth values relatively along line for its segments using PostGIS
I'm trying to make this calculation in SQL with PostGIS:
azimuth_1 + (Sum rel_azimuth_2 to rel_azimuth_7)
rel_azimuth_x is always the angle beetween the actual subsegment_x and one subsegment_x-1 ...
1
vote
1answer
51 views
PostGIS create table with multiple geometry
I want to create a table with a geometry field in it. e.g.:
CREATE TABLE ptgeogwgs(gid serial PRIMARY KEY, geog geography(POINT) );
In my case, this geometry field can either be a POINT, a ...
1
vote
0answers
23 views
Improve OGR SQL query for polygon intersection using WKT geometry
I want calculate the intersected area between a shapefile and WKT geometry.
My target big layer: a.shp
My query small layer: 'POLYGON((1150046 1264030, 1160100 1263793, 1160079 1260606, 1150218 ...
2
votes
1answer
62 views
How can you convert between geographic data from and to 3D data in PostGIS?
Is there was a way to convert a geography value to a 3D geometry value (in ECEF, for example), and vice versa? I know how to convert between geography using a 2D projection, but not a 3D reference ...
1
vote
2answers
79 views
Why do arcpy.GetCount_management and arcpy.ArcSDESQLExecute yield different row counts?
I was benchmarking which method was faster for counting the number of rows in a table, when something curious happened:
import arcpy
import os
import time
test_tuple = ("path\to\connection", "...
2
votes
1answer
51 views
PostgreSQL create line from points
How do i create a linestring from points sort by date in PostgreSQL?
I tried:
SELECT observations.id,
ST_MakeLine(observations.geom ORDER BY observations.date) AS newgeom
FROM observations
...
1
vote
0answers
22 views
PostgreSQL points to line error [duplicate]
I have GPS locations with date. These are points.
I want to convert the points to lines, sorted by date.
Using PostgreSQL
Tried:
SELECT observations.id,
ST_MakeLine(observations.geom ORDER ...
1
vote
0answers
23 views
Hiding one field of geodatabase for some users
I have ArcGIS Server 10.3 and SQL Server 2008.
I have a geodatabase which is connected to SQL Server by using ArcSDE. My users connect to the database by IP address and Username and password. I want ...
0
votes
0answers
27 views
ST_TRANSFORM SDE Oracle does not convert the geometry
I try to convert a geometry from 31370 to 3857. But it does not work. The coordinates stay in 31370.
DB: Oracle 11
ArcSDE: 10.4.1
The code:
select
sde.st_srid(shape)
,sde.st_x(shape) as ...
1
vote
1answer
24 views
pivot-table and group geometries
Postgresql11 - postgis2.4
Here is a table mytbl with points and an attribute var :
geom | var
pt a
pt b
pt1 a
pt1 a
pt1 b
pt2 c
...
I want to "rotate" the table like this :
...
3
votes
2answers
71 views
Distance between points inside LineString
I have a collection of geometries represented by a linestring. Each has at least two coordinates. Is it possible to write a SQL that instead of returning coordinates, return a distance between them.
...
3
votes
1answer
151 views
How do I use pre-existing Lat/Long data in a SQLite table with QGIS?
I have an application that stores its data in a SQLite database. I can't modify the application or database to add geometry extensions, but it does already have latitude and longitude columns for each ...
1
vote
0answers
29 views
Use selected features to generate WHERE clause in ArcGIS for Portal
I have a web map in Portal that has a point layer called Work Orders.
I believe it's possible to manually select points in the layer by using the Web AppBuilder Select Widget.
Using selected points, ...
4
votes
1answer
45 views
Aggregating multiple rasters within a polygon in PostGIS
I have several raster tables in PostGIS (four in fact but in this case let us assume there are just two) - same cell sizes, same alignment but with different types of value (e.g 3d_pot, 3d_area). ...
1
vote
1answer
25 views
Adding Encryption options to GeoServer SQL Server connection
I have set up an HTTPS connection between GeoServer and an Azure database using the SQL Server extension, however I would like to add encryption options to the connection string. Is this possible in ...
3
votes
1answer
77 views
Limit on Filter Query Stipulations?
I am novice mapper. I ran into some trouble with QGIS today when trying to add additional census tracts to a layer filter.
The tract exists within the layer I'm filtering, but there seems to be a ...
0
votes
1answer
28 views
ArcMap SQL Trim Def query code
How can I get this ArcMap SQL Trim Def query code correct?
I have a attribute field that looks like:
LOC: R1, THEN A BIG LONG BUNCH OF WORDS
or
LOC: L10, THEN A BIG LONG BUNCH OF WORDS
What I need ...
1
vote
1answer
54 views
Error while using ST_Line_Interpolate_Point: first argument isn't line
I've been running a scheduled task that contains some SQL code that has been running fine up until a few weeks ago. I don't believe any of the code had changed recently so I went through the debugger ...
1
vote
0answers
22 views
Unexpected query layer results: Why does unioning with DUAL fix it?
Simple Query:
I have a query that connects to a table in a work order management database (Oracle 12c; non-GDB).
select
wo.wonum
from
maximo.workorder wo
The query returns 428 records in ...
1
vote
0answers
74 views
Speed up PostGIS raster query
I have a simple PostGIS table as follows:
\d rad;
Table "public.rad"
Column | Type | Collation | Nullable | Default ...
3
votes
1answer
83 views
Using ST_Segmentize to PostgreSQL table of segments?
I have a PostgreSQL table with straight lines (segments) that I need to subdivide in further segments so that none of them is more than 50 metres long. I am new using PostGIS (and SQL) and I'm ...
1
vote
0answers
17 views
Setting constraint so that new value in “Table A” must already exist in “Table B”, else return error? [closed]
I'm trying to set up some kind of constraint so that I can only enter values in "Table A", if that value exists in "Table B".
I want this to act sort of like a selection list, where there are only a ...
5
votes
1answer
217 views
Concatenate and sequentially order unique groups of lines using SQL
I have a segmented line (road) dataset that potentially runs through multiple area polygons (localities). I want the road dataset that is grouped by the unique field (RoadMntnc) to have a concatenated ...
0
votes
0answers
59 views
Convert Polygon to MultiPolygon using features attributes
I'm trying to convert SHP files to GeoJson using ogr2ogr and that works well with Polygon.
Now I'm struggling to convert multiple Polygon geometry features to a single one (MultiPolygon) for each ...
3
votes
1answer
60 views
Clustering geometry points, return a point's label if the point does not belong to a cluster?
I am clustering geometry points using the k-mean algorithm. The result is a list of clusters. Some clusters contain multiple points. Others only contain a single point. On the client side, we would ...
0
votes
0answers
33 views
Getting geodatabase to SQL Server
I am currently attempting to load in 1000 data sets I have in 1000 different tables. I need to do this in Microsoft SQL Server.
My data sources are either in Excel or geodatabases. The plan is to ...
5
votes
1answer
242 views
Getting the line middle point with SQL in QGIS
Using SQL in a Virtual Layer is it possible to get the midpoint of a line on the line?
I have tried Finding middle point (midpoint) of line in QGIS? but got an error
I also tried
SELECT road, ...
0
votes
1answer
36 views
Loop through geodatabases and list feature classes [closed]
Trying to find a way to use Python or SQL to loop through geodatabases and print the feature classes. I'm not sure what the difference between while and for loops are; I'm brand new to scripting. A ...
