Questions tagged [sql]
Acronym for Structured Query Language. A syntax for retrieving and manipulating data from a relational database.
1,351
questions
1
vote
1answer
50 views
Joining table data with expression in QGIS
I use QGIS 3.4 as a beginner.
I have a vector layer 'Address' with the fields:
"Id"(int)
"postalcode" (str)
"straatname"(str)
"housenr"(int)
and a CSV-file with the fields
"section" (int)
"...
0
votes
1answer
46 views
Spatialite - update query for bounding polygon using WITHIN
I have 2 spatialite polygon layers which represent land parcels and municipalities. Each parcel is within or intersects 0, 1 or 2 municipalities. I am trying to update a field within the parcel layer ...
0
votes
0answers
18 views
QGIS 3.12: Importing shape (point) to PostGIS database as points without Z values
Currently I am importing vector layers (points) to a PostGIS database via QGIS 3.12. Everything works great expect for one part: after the import is complete all my points have a Z value (which is ...
2
votes
1answer
35 views
Using ArcGIS Pro to select all data collected “Yesterday” regardless of the time of day
I am using ArcGIS Pro 2.4.3.
I can upgrade if needed.
I am reading a hosted feature layer from my Organisation's AGOL site in ArcGIS Pro. This is using the standard Esri Data Store. This dataset ...
3
votes
1answer
70 views
SQL Syntax Error in QGIS near 'DELETE' [closed]
I can't understand what is wrong in my syntax, or if this is specifically an issue with QGIS but I am hoping someone can explain why I am still receiving a syntax error for this.
I am trying to ...
4
votes
3answers
146 views
How to group by / Order by with logical sequence
I have a problem in SQL (QGIS). I have a shape with plots. I would like to obtain a certain organisation.
Here a schema of what I have and what I want :
3
votes
1answer
34 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 ...
2
votes
0answers
36 views
ST_Union 'MIN' on rasters with result raster having DN values as raster table column value
I am new to working with rasters in PostGIS. Although I have spent couple of days trying to find solution for my problem, I couldn't seem to crack it without some help.
Here's my problem:
I have ...
0
votes
1answer
30 views
ArcMap: Identify mismatch between position of points from database with their actual location in polygons
A map in ArcMap contains two datasets:
Points J_i from table Jurisdiction of an SQL-database (SQL Server 2017) displayed by Display XY Data (Data Type: XY Event Source)
Polygon shapefile feature ...
0
votes
0answers
36 views
Problem by using PostgreSQL type PATH with SQLAlchemy
I have a PostgreSQL table below with a field coordinates of type path:
CREATE TABLE geometry
(
id serial not null primary key,
coordinates path not null
);
And I am trying to ...
1
vote
0answers
14 views
Adjusting nodes in SQL
The intranet our company is using is combining shapes to form larger shapes. This is using code that is lost in the darkest realms of extremely bad practices, but for the most part it is okay.
...
3
votes
1answer
66 views
Set SRID in all tables on a schema
I have a lot of tables with column geom (Multipolygon) without SRID in PostgreSQL/PostGIS.
I need to set all tables with a SRID of 3003.
This is my first time with SQL function.
I'm using a part ...
1
vote
1answer
22 views
PostGIS match geometry in another table
I'm trying to find records from two tables with matching point geometry. The tables have different EPSG types. I do this:
SELECT count(*) FROM t1
WHERE st_intersects(t1.geom, (SELECT st_transform(t2....
1
vote
1answer
23 views
PostGIS: ST_ClosestPoint join error
I have two tables: one with geom points of species and an other one with height contour lines geoms. My Idea was to find per specie geom the closest point to a contour 'geom' line and then select its ...
1
vote
0answers
15 views
ArcGIS linefeed character for Identify window using SQL View
I've searched everywhere but have been unable to find the solution. Using a view from SQL I have a semicolon separated field that when I info each feature I want to have a line break in the results ...
0
votes
2answers
45 views
Creating a view in ArcCatalog - ORA-01722 [closed]
I'm creating a view in ArcCatalog 10.6 on an Oracle DB. Never created a view before but with some Googling, I've managed the SQL below. The view works but only partially. In ArcCatalog, preview shows ...
0
votes
1answer
34 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
24 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
36 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
25 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 ...
0
votes
0answers
65 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 ...
2
votes
1answer
33 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
28 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
25 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
25 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
29 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
43 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
37 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
22 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
164 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
88 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
90 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
51 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
83 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
40 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
44 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
38 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
49 views
Length of longest street using 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
62 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
59 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
68 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
29 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
65 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
52 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
145 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
45 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 ...
