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
0answers
7 views

ArcMap - Definition query with subquery using a date returns wrong result

In ArcMap 10.6, I am using the following definition query on a featureClass to get the features that are underway but the results is not what I expected. Data are stored in an Enterprise geodatabase ...
0
votes
0answers
29 views

Translate Excel functions to SQL/Python [closed]

I am trying to calculate different raster layers in ArcGIS with a relativly simple formula, but a few more complicated cell relationships. I have already solved the calculation in Microsoft Excel and ...
2
votes
3answers
71 views

How to convert UK grid to coordinates in PostgreSQL?

I have a very large file with UK grid references that I need to get coordinates from I'm on a Mac so I cant seem to use the solution provided in this link https://digimap.edina.ac.uk/webhelp/os/...
1
vote
0answers
45 views

Using QGIS field calculator expression on Execute SQL

The following expression copy only minimum value from group/subset to a new column field in QGIS. Case when "FID_Net"=minimum("class", "FID_Net") Then "FID_Net" Else 0 End But this expression on ...
1
vote
1answer
29 views

QGIS - Execute SQL Failure

I've written an SQL query which works in the query section of the QGIS DB Manager. SELECT Max(S_order), SegmentID, S_Order, Length, Buff_Segme, Buff_Order, Buff_Lengt, Buff_Uniqu FROM "...
1
vote
1answer
37 views

Selecting elements by grouping with one attribute and specific rule using QGIS

I have a question about a specific query for a Shapefile-Layer in QGIS 3.4. I have a Point-Shapefile with the name point.shp. The Shapefile has a lot of different points with a large amount of ...
0
votes
1answer
27 views

Select evenly distirbuted points of unevenly distributed set

I have a task which looks pretty nontrivial yet pretty standard. Let's say, I have a set of points, unevenly distributed on the map (i.e. in some regions there are a lot of points, while in the ...
1
vote
1answer
31 views

Length of a street-Spatial SQL

I have a table with the geometry of streets and I`d like to find out, which is the longest and which length (in kilometers) it is. How can I summarize the individual street segments?
2
votes
1answer
53 views

Perform intersection using a TRIGGER

I've two geometry table inside my DB: buffer: id INTEGER SERIAL PRIMARY KEY, geom POLYGON polygons: geom MULTIPOLYGON I need have the intersection between buffer and polygons and I will do this ...
0
votes
1answer
50 views

Using SQL query in QGIS Database Manager?

I have below query which I am supposed to import into QGIS by amending certain aspects, to find out exactly what it does. But when I try to run it on the corresponding PostGIS layer, DB Manager warns ...
1
vote
1answer
52 views

QGIS - SQL GROUP BY and MAX

I have a layer which contains a unique ID (UID) for each feature and another column of Scores. For example: UID, Score A12,1 A13,1 A14,4 A12,2 A13,3 A14,5 I would like to use an SQL statement to ...
0
votes
0answers
26 views

Getting SQL explain-plan for ArcGIS Map service

My organization has an ArcGIS map service that contains registered views from an Oracle 18c/10.7.1 geodatabse. I'm having some performance issues with the layers/views in the map service. I want to ...
1
vote
1answer
22 views

Cleaning SQL queries [closed]

I have a bit of SQL code that look similar to this: SELECT sum(case when latitude = '0' then 1 else 0 end) as count_zero, sum(case when latitude is NULL then 1 else 0 end) as count_null, sum((case ...
0
votes
2answers
60 views

Isolate the name of the city from an address

I have a data set in CSV format that includes the name of the store, full address, latitude and longitude (from Geocode). Since address is in full format, is there a way I can get a special column ...
3
votes
2answers
41 views

ogr2ogr selecting features by location

I'm trying select features from one shapefile that contains features from another shapefile. However when I run the command I get an error " no such column geometry" Both shapefiles are working ...
4
votes
1answer
103 views

ogr2ogr commands with cmd

I'm trying to run a ogr2ogr command with cmd. When I run the cmd the Osgeow command prompt just flashes and nothing happens. When I enter my ogr2ogr script directly to the Command Prompt everythinh ...
0
votes
0answers
35 views

Sql query with ogr2ogr for clipping polygons

I'm trying to build a query with ogr2ogr to clip intersecting polygons. I know how to do this with QGIS database manager sql but I can't get it to work with ogr2ogr. With information I've gathered ...
1
vote
0answers
23 views

ArcGIS -ArcMap Advanced SQL Expression Returning Blank Output

Want to return the comments that only show "Pending Annexation - Pending City Limits", but is returning a blank value.
0
votes
3answers
40 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://...
1
vote
1answer
18 views

How to create updateable/inseartable view on QGIS with a sequence id?

I have a huge point dataset that takes a long time to insert new stuff when using QGIS This table has an id column of type serial To go around this problem I've created an empty view that has the ...
0
votes
1answer
39 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
20 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
17 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
1answer
41 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
96 views

Get midpoint of SDE.ST_GEOMETRY polylines?

I have a polyline SDE.ST_GEOMETRY feature class in an Oracle 12c geodatabase (10.7.1). I want to create a view on the feature class that has X and Y columns that contain the coordinates of the ...
2
votes
1answer
36 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
24 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,...
4
votes
2answers
101 views

PostGIS doesn'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
48 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
38 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
46 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
75 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
47 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 ...
2
votes
1answer
30 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
36 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
14 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
53 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
37 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
44 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
135 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
107 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
56 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
27 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
84 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
69 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 ...