Questions tagged [sql]

Acronym for Structured Query Language. A syntax for retrieving and manipulating data from a relational database.

251 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
5
votes
1answer
98 views

Joining points using PostGIS

I have a set of points which represents a series of telegraph poles. I want to link them to simulate the wire running along them. The code I've been using is shown below. SELECT DISTINCT ON (p1.id) ...
5
votes
0answers
483 views

Creating SqlView Layer with GeoServer Manager?

I would like to register a new SQL view layer to GeoServer through the REST API, I am using geoserver-manager. The registration is OK, the layer is working fine, except the WMS GetFeatureInfo, which ...
4
votes
0answers
60 views

ogr2ogr creates too many columns with LEFT JOIN

I am creating a GeoPackage using several shapefiles using ogr2ogr (GDAL 2.2.4): ogr2ogr -f GPKG /path/to/out.gpkg -append -nln out -a_srs EPSG:4326 /path/to/in1.shp ogr2ogr -f GPKG /path/to/out.gpkg -...
4
votes
1answer
80 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). ...
4
votes
0answers
175 views

Problem with st_clusterWithin and conditions

I need help because I am always in front of a problem in PostgreSQL since several weeks: I have the following table (extract): Id for the identifier geom for the geometry of the polygon burndate ...
4
votes
0answers
418 views

Preserve datatypes when using SQLite and QGIS

Following Nathan's SpatiaLite/DBManager solution in this answer, I find that once the layer is loaded from DB Manager into QGIS (2.6.1), any calculated fields are appearing as text values instead of ...
4
votes
0answers
888 views

Geoserver WMST Date Format

There is table in SQL-Server 2008 R2. In this table I have date column with date format like this 2006-09-08 14:39:41.000. Now I make a view from this table on GeoServer side and do nothing with the ...
3
votes
0answers
56 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 ...
3
votes
0answers
397 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 ...
3
votes
2answers
543 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 ...
3
votes
0answers
133 views

Using SQL intersect query?

I am trying to find the length of a polyline inside a polygon using this query: declare @p geometry, @l geometry; select @l = SHAPE FROM [Vz_DSM_Stationing].[dbo].[Route_Sections] where OBJECTID = 3;...
3
votes
0answers
402 views

Using function in pgPointCloud extension to PostgreSQL with PostGIS

I am using the pgPointCloud extension to PostgreSQL and have created a table of blocks/patches of points in a point cloud. Now, I am just trying to run a few of the functions in the pgPointCloud/...
3
votes
0answers
386 views

Virtual layer from query with special characters in field name using PyQGIS

I have a layer loaded from a WFS server that contains fields with æøå in some of the names. I am making SQL queries to make virtual layers from this layer, but whenever I query a field with æøå in its ...
3
votes
0answers
621 views

Update sde.st_geometry M values to cumulative distance (geometric length)

I'm trying to write a query to update sde.st_geometry M values to the cumulative length of a line. The M values in my route feature class are purely based on the geometric length of the line. The ...
3
votes
0answers
73 views

SOL help in QGIS: rule based styling to show timestamp increase via gradient

I have some polygon data from a tractor that shows it's route among other things. It has a timestamp column which I'd like to use to show the route in which the tractor drove through the field via a ...
3
votes
0answers
302 views

Where do field aliases reside in an SDE database?

If I want to get field aliases for an SDE table through pure SQL, what table do I query? My geodatabase was created with ArcGIS 10.1 in an Oracle database.
3
votes
0answers
306 views

How to use pythons' GeoAlchemy2 to filter on dynamically generated Points?

I'm trying to do something like this SQL query in Geoalchemy2: SELECT district_polygons.district FROM markers, district_polygons WHERE ST_Contains(district_polygons.multi_poly, Point(markers....
3
votes
0answers
104 views

QGIS - WHERE SQL within a processing.runalg statement for v.surf.rst in python console

I'm creating lots of raster files with grass:v.surf.rst so rather than using the graphical interface I wanted to use the python console (and potentially put in in a script at some point). However ...
3
votes
0answers
605 views

Error on QGIS with PostgreSQL views?

I have a PostGIS database with various tables and views. With pgAdmin I can make requests on the tables, on the views, all is working. I add tables in QGIS 2.8, it's working, I can read, write, modify ...
3
votes
0answers
250 views

Visualizing variability in the size and shape of polygons

I am working with a data set of area restrictions imposed by criminal courts. These restrictions ban a person from entering a demarcated area. These restrictions vary in their size, shape, and ...
3
votes
0answers
870 views

Tables from SQL Server to QGIS Layers

I'm a beginner SQL and GIS user and this my first time using stackexchange, so apologies if I'm asking the question properly. I'm trying to open some MSSQL tables in QGIS. I established a connection ...
3
votes
0answers
914 views

How to export data from sql server into shapefile in the decimal format in QGIS 2.4?

The last couple of days, I have been working on exporting data from SQL to a shapefile in QGIS 2.4. While converting into a shapefile, the data type changes automatically from Float to Integer in the ...
3
votes
0answers
124 views

How to express not within/inside predicate in spatial database with good performance

In our application we want to express some spatial predicates as objects not within some areas, but unfortunately it has very bad performance. The following SQL is generated by our application: ...
3
votes
0answers
4k views

PostGIS Better ST_Intersects Query

I am currently using the following queries to return all features that intersect a large and complex polygon. I initially tried to use a single ST_Intersect query but because the number of features it ...
3
votes
1answer
436 views

What's the correct syntax for Execute SQL in Processing QGIS 2.6?

I made a small model with Processing in QGIS 2.6. The final layer should be the result of a SQL query. Now, in the Execute SQL dialog window I can add the query: select name, max(ele) as ele, geom ...
2
votes
1answer
96 views

Find all closest points to unique point in PostGIS

I have two tables valid_collisions (POINT) and intrct_pts (POINT) I want to create a new table using a nearest-neighbor type of query to find aggregate values from my valid_collisions. In essence I am ...
2
votes
0answers
67 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
138 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 ...
2
votes
0answers
77 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 ...
2
votes
1answer
103 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
187 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), ...
2
votes
0answers
58 views

Calculating share of overlapping areas via aggregate function in a virtual field

I'm trying to calculate the shared area for several overlapping areas. I need a dynamic solution in order to get automatically calculated statistics when drawing new polygons. The target area is in ...
2
votes
0answers
37 views

Seeking syntax for adding query to spatial view from related table in ArcGIS Desktop?

I'm just learning SQL so apologies for the poorly worded question. I'm basically trying to create a spatial view of hydrants that have been painted in 2018. I have a hydrant feature class (wHydrant) ...
2
votes
0answers
81 views

How can I validate a SQL statement in ArcObjects?

I'm trying to validate the results of a ISQLQueryDialog2, but can't find any out of the box solutions. This particular dialog has a built in validate button, but I would like to programmatically ...
2
votes
0answers
241 views

T-SQL Invalid Geometry Created by Intersection

I have two sets of geometries (A and B) in SQL Server 2016. I want to determine the overlap areas of those geometries from set A that intersect set B. Most of the time this code works: SELECT a....
2
votes
0answers
335 views

What is a general purpose of “Update SQL layer” in QGIS 3?

Can someone explain to me, what is a general purpose of "Update SQL layer"? When I "update" it becomes not editable... (see gif ▼)
2
votes
0answers
75 views

postgis and st_intersects ; fail to compare a table to itself with a left join

postgresql 9.6, postgis 2.3 Following this Select features that do NOT intersect in PostGIS, I would like to compare one table to itself, to keep all records from table p1, even and above all those ...
2
votes
0answers
56 views

Select events that represent the most recent work on roads

I have a question on Stack Overflow where I ask about Selecting events that represent the most recent work on roads. So far, I have been provided an answer that seems to work, although it is quite ...
2
votes
0answers
255 views

QGIS virtual layer with SQL GROUP_CONCAT performance

I have a polygon shapefile layer, Property, with property ownership parcels and a SQLite database with projects. My goal is to highlight the parcels where an entry in the Projects database and show a ...
2
votes
0answers
682 views

SQL Geometry type and Geography type conversions/projections

I am working with a replica database managed in Sql Server Management Studio 2016. My geographic data of points, lines, and polygons are projected in WGS_1984_UTM_Zone_17N Meter and SRID of 32617. Its ...
2
votes
0answers
126 views

Odd output for PostGIS st_snap

I'm trying to snap a line theme to a point theme using PostGIS with a filter so they only snap to records with the same reference number. The query I'm using is as follows (this is a sample of the ...
2
votes
0answers
263 views

SQL geography::EnvelopeAggregate output not accurate

I am trying to get the geography::EnvelopeAggregate for a set of locations. The geography column values are calculated using the Lat/Long for individual points. When I try to fetch the boundary box(...
2
votes
0answers
37 views

How to add read-only SQL Textbox calculated from a SQL statement in Qgis Form field

I have two tables which have a relation one to many, because the second table stores the editing history of the first table which has geometry, and they are connected through an ID. I want to create ...
2
votes
0answers
234 views

PostGIS SQL Editor plugin - path to pgsql2shp

I'm new to PostGIS/QGIS, and I was looking at using the PostGIS SQL Editor plugin (v.2.0.0) instead of the OpenJump tool recommended in PostGIS in Action 2nd ed. However, I am having problems ...
2
votes
0answers
139 views

Create multipolygons grouped by field, ogr2ogr

There is a large polygon data set which I would like to aggregate some of the polygons with the same values in a specific field to multipolygons. I would like to do this using ogr2ogr. I have the ...
2
votes
0answers
404 views

Oracle Spatial: Error with SDO_UNION

I have a table which contains address-points. With following sql-statement i have created a 100-meter buffer around each address-point: CREATE TABLE BUFFERPOLYGONS AS SELECT a.OBJECTID, SDO_GEOM....
2
votes
0answers
79 views

postgis query performance

The db structure is more or less like this and can't be modified. I can only add some indexes but can't set for example foreign keys: CREATE TABLE adresspoint ( id character varying(3999) NOT ...
2
votes
0answers
112 views

PostGIS Nearest Neighbor query performance - can it be quicker?

I want to canvas the community to see if my nearest neighbor query can be optimized at all. I am happy with my current results, but I'm always up for improving. I have 2 tables, lakes and towns. I ...
2
votes
0answers
632 views

Between operator QGIS

I am using QGIS Essen version (2.14.1). I am trying to filter my data based on dates. I am wondering if the "between" operator exists like in SQL? I'm trying the following query but the results are ...
2
votes
0answers
46 views

Programatically populate field_4 after choosing values from other fields?

I'm working with QGis 2.8, editing the attribute table of a SHP file, named "myTable". I need to populate field_4 with the conditions listed below: For 1 to n (it runs across records of myTable) ...

1
2 3 4 5 6