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

1
2 3 4 5
28