Questions tagged [sql]
Acronym for Structured Query Language. A syntax for retrieving and manipulating data from a relational database.
1,337
questions
-1
votes
0answers
23 views
Postgres convert PATH type to ARRAY
Is there any way to convert Postgres PATH type to an ARRAY in order to have index access to it's points?
0
votes
1answer
32 views
SQL query builder in QGIS 3 doesn't display columns names
I'm creating sql queries using QGIS 3.8 DB Manager.
The problem i'm facing is that when I write a query, I don't have any option to display the columns names :
Is there any way to display the ...
0
votes
0answers
13 views
QGIS Ubuntu 3.4 SQL Query Composer in WFS Connection Dialog
The QGIS 3.4 documentation mentions the procedure of filtering the features of a WFS through the connection dialog box either by double-clicking on the layer of interest, or by clicking the "Build ...
1
vote
1answer
33 views
Drawing a directed line based on dataframe indexes?
I trying to draw a directed graph based on a geopandas dataframe with the Cartoframe library.
The Geopandas dataframe has the following structure:
deviceId routeLoad longitude latitude ...
0
votes
0answers
24 views
Using search cursors with SQL in Python
I'm trying to create a search cursor that will list the cities in which the population is greater than 1,000,000. I was able to create a list of the cities and their populations, but unsure of the ...
2
votes
1answer
45 views
SQL query for private OSM Server
I'm trying to make a SQL query in my own OSM server which filters are the tag of the object (for example k = 'highway') and the name of the user that created the object (for example display_name = '...
0
votes
0answers
59 views
Many to many relationship in a QGIS Geopackage - fail
The current user manual has been unsuccessful in directing me to establish a many to many db relationship in a geopackage in QGIS (3.10.2 on Ubuntu w gdal 2x).
After the airlines in the manual ...
1
vote
1answer
23 views
Attribute stats query with Execute SQL tool in QGIS Modeler
I'm trying to get a simple temporary table with the surface of different categories of a polygon shapefile, classified by category. My final aim is to hide an SQL query in a tool with optional ...
1
vote
1answer
27 views
Is there a way to create a new, pre-populated field within a view (Feature Layer), in Arcpy?
In SQL, users can write a SELECT statement that 'invents' a new field with a default value already populated in it, for all records returned. In the example below, the field Type doesn't actually ...
0
votes
1answer
20 views
Unable to connect to SQL Server tables with 10.5 ArcMap but it works with 10.7
I have a requirement where I need to connect to the SQL Server which I have created using ArcCatalog 10.7.
I have a requirement where I need to now use 10.5 version. But when I attempt to connect to ...
1
vote
1answer
20 views
PGrouting costs
Most algorithms have two functions:
A normal one: like dijkstra()
A Cost-version: like DijkstraCost()
I want the result of the cost version: A fast computation of the total seconds.
In mine edges ...
2
votes
0answers
25 views
Get length of a line stored in SQL Server Geometry column with srid 4326
I have some spatial data stored in SQL database geometry column called sp_geography (The field is defined as Geometry). The SQL below:
f.sp_geography.STLength()
returns, for example 0....
2
votes
1answer
42 views
PostGIS: Extract intersected areas with their related column values
I have inserted a MultiPolygon table in PostGIS with the following columns:
id, e_id , p_id, com, geom
Assume that I have 4 polygons with overlapped areas with the following values:
id com geom
1 ...
3
votes
0answers
49 views
Function to make a trajectory more straight
I'm new at PostGIS and I have to do a project for my university course.
I'm trying to analyze a trajectory of points that I downloaded from my runtastic app but it's a mess.
There are some points ...
1
vote
0answers
31 views
Convert ellipsoid height to geoid using SpatiaLite
I would like to convert ellipsoid height to geoid (EGM2008) using SpatiaLite. is there any query that we could achieve this?
1
vote
1answer
21 views
Display map object from SQL database in MapInfo with read only access to the database
I can connect MapInfo to a SQL database using a select statement to display the contents of a table in the SQL database. However, the table is not mapable and I only have read access to the database ...
7
votes
2answers
145 views
Aligning line with closest line segment in PostGIS
I've got a road network dataset and a dataset that consist of multiple disconnected lines of varying lengths in PostGIS.
Most of these lines are (largely) parallel to the road network, but the ...
1
vote
1answer
44 views
How to merge 2 huge tables with only geometry in common without duplicates
I've got 2 tables with several millions of rows. Because of poor data management, they don't have any ids at all or anything that helps match them up except for the geom
I've tried doing
insert into ...
0
votes
0answers
65 views
Export a layer from QGIS to Oracle DB
I have a working connection to an Oracle database in QGIS (3.4). I am able to import and edit existing tables into my QGIS project out of the db. Unfortunately it is not possible to export any QGIS ...
2
votes
1answer
76 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 ...
1
vote
0answers
49 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 ...
2
votes
3answers
78 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/...
2
votes
0answers
53 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
35 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
39 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
31 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
33 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
59 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
57 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
57 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
28 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
23 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
64 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
48 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
132 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
38 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
43 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
19 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
41 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
23 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
21 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
54 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
131 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
39 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
116 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
65 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
44 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
58 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],
...
