Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.

Questions tagged [sql]

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

208 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
9
votes
0answers
238 views

How do I select the longest connected lines from PostGIS ST_ApproximateMedialAxis?

I used PostGIS ST_ApproximateMedialAxis to transform polygons representing mountain ranges into a series of lines. Now, I'd like to select only the "longest" line segment for each group, so that I ...
4
votes
1answer
43 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
1answer
67 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)...
4
votes
0answers
145 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
837 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 ...
4
votes
0answers
456 views

Geoserver 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 wms getFeatureInfo, which ...
3
votes
0answers
115 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
319 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
525 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
68 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
244 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
102 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
533 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
239 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
387 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 ...
3
votes
0answers
846 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
901 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
418 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
0answers
24 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
50 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
34 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
39 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
109 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
135 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
51 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
55 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
193 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
338 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
101 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
231 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
34 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
204 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
116 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
348 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
78 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
420 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) ...
2
votes
0answers
693 views

How to upload data from QGIS to a SQL Server?

I'm new at QGIS. I'm wondering if there's a native way or plugin to allow me to upload a shapefile into my SQL Server directly from QGIS. So far the only solution that I could find is the Shape2SQL ...
2
votes
0answers
50 views

CartoDB geom (coordinates) join: without exact match

Trying to join two datasets (layers) on CartoDB based on their geographical fields (in CartoDB's the_geom created field). However, the basic ST_Intersection function does not work since its tolerance ...
2
votes
0answers
293 views

How do I create an attribute table in a web page from GeoServer using JavaScript?

I'm trying to create a custom web application using Leaflet and Geoserver. I've got my layers visible in a map viewer, but I'd also like to show an attribute table of the visible map layers. I can't ...
2
votes
0answers
34 views

Write userID to field automatically every time an object is edited

Currently every user has to put his/her useriD in a field after every change made to an object in the database. This takes time and can be forgotten easily. Is there a SQL-Command giving back the ...
2
votes
0answers
249 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....
2
votes
0answers
732 views

ogr2ogr conversion error - Can't create fields of type Binary on shapefile layers

I'm having trouble using the ogr2ogr tool on the command line. My data is stored in SQL as binary, type is mixed polygons and multipolygons. I am using the following command for conversion from SQL to ...
2
votes
0answers
233 views

ogr2ogr: export intersected gml using shapefile

I'm trying to do the same as the ogr2ogr example given by https://gis.stackexchange.com/a/119404/6458 The difference is that my inputs are a GML and a shapefile. Export.shp should only contain the ...
2
votes
0answers
57 views

Aligning Points in Decimal Degrees

Running ArcMap 10.3 I am displaying a layer of manholes from a table coming from SQL. The XYs are in decimal degrees however they are displaying no where near my other layers (NYC PLUTO). I have ...
2
votes
0answers
675 views

set Default projection in postgis table

I've set the SRID 4326 for a table having geometry column, but when i insert a geometry without SRID, i get an error because no SRID specified is interpreted as -1 or 0, so how i can set a default ...
2
votes
0answers
231 views

ST_Buffer with segment AND endcap parameters

Making a QGis plugin that buffers a line and I want to declare a high number of segments and a flat endcap. ST_Buffer(geom, 40, 25, 'endcap=flat') This doesn't work with both parameters entered, it ...
2
votes
0answers
223 views

SQL Error Message: Find and Replace for Date Field

I am attempting to use the find and replace function within the Attribute Table Options to replace fields that contain dates in the following format: month/day/year (e.g. 03/14/2014). The field's ...