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
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 ...