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
0 answers
56 views

Trigger on PostGIS line layer occurs twice [closed]

Using this tutorial, I've established a SQL trigger in order to track changes on a PostGIS layer. It works great for a point layer. However, using the script below, it causes problems with line layer, ...
1 vote
0 answers
96 views

Updating spatial table in Oracle database with SQL query in PyQGIS?

I am trying to use the Python Console within QGIS 3.4 to update a spatial table in an Oracle database. The goal is to update all values in the field DTM_ELEV in the Oracle table with values from a ...
8 votes
4 answers
4k views

Using Sqlite, Python 2.7 and Spatialite?

I am trying to spatially enable my sqlite database: import sqlite3 db = r'c:\temp\mydata.sqlite' conn = sqlite3.connect(db) conn.enable_load_extension(True) cur = conn.cursor() cur.execute(r"SELECT ...
4 votes
0 answers
1k views

Open GPKG, query layers and save to new GPKG [closed]

I have recently started to use SQL and geopackages queries in R. I know how to open it, access the individual layers (geometry and tables), and select and process individual layers. But, I don't know ...
1 vote
0 answers
160 views

Creating virtual layer or view table from original table using pivot table on QGIS?

I have a table and I need to produce a virtual layer with pivot or crosstable command. Is it possible on QGIS 3.6 or 3.8? My original sql is: select cd_setor, Name, Path_relativo from Fotos order ...
0 votes
0 answers
44 views

Login form for loding layer from MS SQL Server

I created database and featureclass and table in SQL server. I loaded layer in QGIS, my UI editing and veiw from, designed by qt designer and saved to QGIS project. Now I need to create login form: ...
3 votes
1 answer
308 views

Performance of arcpy.Exists() in SQL Server enterprise gdb environment

While iterating on a list containing table names, I used arcpy.Exists() to check if a table/featureclass exists. This action is very time consuming. I've ended up using a database approach select ...
0 votes
1 answer
256 views

Improve Performance of ST_DWithin - H2GIS

I have a table seg_point, that I create like this: CREATE TABLE seg_point (id INT AUTO_INCREMENT PRIMARY KEY, segmentid TEXT, geom GEOMETRY); And I'm trying to improve the performance of this query ...
1 vote
0 answers
58 views

Select features of a shape which are not in another shape

I have a shape with some features (let's say 100) and a second shape with some of this features (50) buffered (5 meters inside buffer). I want to select and write in a new shape every feature of the ...
1 vote
0 answers
137 views

Determining which latitude and longitude values fall within township/district/polygons

Is it possible to export the boundaries of one layer, as well as the latitude and longitude falling within each boundary, from a program/by programming? I seem to have gotten fairly close to a ...
0 votes
1 answer
567 views

Datatype changed to qlonglong from integer when export to shp and SQL

I have a view that basis on the table 'mytable' with these columns: The view created as CREATE or REPLACE VIEW viewbox.myview AS SELECT mytable.h AS height, mytable.geom FROM mytable; with columns ...
2 votes
1 answer
2k views

SQL: How to union polygon geometries from a single table that have the same id and certain type?

Essentially, the table looks like this (geometries are mostly unique, and shouldn't overlap, but may overlap a little): id sub-id type Geometry 123456 1 ordinary ...
2 votes
1 answer
488 views

Merging two different geometry layers into one (SQL, GeoPackage)

I have a point layer ('points') and a polygon layer ('multipolygons') . I want to load the points layer combined with the centroids of the polygon layer. Im using GeoPackage and tryed to go a SQL ...
2 votes
2 answers
597 views

FME-SQL where ID in id.list

I extracted and transformed worksite records from a Oracle dB and stored them in a PostGIS dB. There are about 700 worksite records having a unique ID. I need to extract additional worksites from the ...
0 votes
1 answer
3k views

Error: Geometry constraint geom-type or SRID not allowed. How to ensure the correct geom-type?

Following the top answer on a previous question about storing geometries in a spatialite database, I am trying to update a spatialite table using: engine.execute("SELECT AddGeometryColumn('your_table'...
1 vote
1 answer
54 views

Renaming duplicates with any vacant value within a certain interval

The values of my column of interest (col1) should range from 0-999. I'm looking for a way to assign one of each duplicate a new value that lies within my this range 0-999. Only features that are ...
2 votes
1 answer
492 views

PostgreSQL - Array of overlapping Polygon Ids

I have a database table (overlay) that has columns, id::int, intersections::int array, geom::polygon. I have another table (images) that has id and geom columns. I am trying to populate the ...
-1 votes
1 answer
114 views

How to get points around one point buffer area in a table [closed]

I have many points in a table. I want to show points near one point. Buffer area can be 10 or 100 in SQL Server using SQL query.
2 votes
1 answer
323 views

Finding nearest point for each distinct category in performant way using big PostGIS table?

I have a table, etablissements with millions of rows, and a geom (st_point) column , spatially indexed (using gist). Those features may have a different category, activite_principale. I want to find ...
1 vote
0 answers
153 views

How to get geographic data of any particular region of map in Geometry datatype format in SQL Server? [closed]

I am completely new to Geometry datatype format of a Microsoft SQL Server database. DataType is Geometry. If not wrong, geographic data contains latitude and longitude of each point of that map, ...
3 votes
2 answers
1k views

Add spatial functions to a geopackage template

I would like to dump data from my survey software to a geopackage file through an ODBC connection but I need a minimum of spatial functions to execute my requests: ST_IsEmpty(geom), ST_MinX(geom), ...
0 votes
1 answer
833 views

Selecting max value in SQL query from Extract by Attributes?

I currently have a raster that has 3 columns in the attribute table: OBJECTID, Value, and Count. As part of ModelBuilder, I am running this raster through the Extract by Attributes tool. The Extract ...
1 vote
1 answer
341 views

Polygon node order will not correct [duplicate]

I've got a few polygons (they were previously one multipolygon) which seem to insist on having an anticlockwise node order. As such, when running through various operations in SQL, they throw out the ...
15 votes
3 answers
6k views

Join intersecting lines with PostGIS

This is something that I'm almost ashamed to ask but I can't seem to get it to work for the life of me. I have a road layer with segments, each segment has a Road ID and a segment type. I would like ...
2 votes
1 answer
567 views

Creating Spatial View ... GIS/SQL

I have created an enterprise geodatabase in my local instance of SQL Server and I want to create one select statement of multiple select statements within multiple tables and feed this into a view. ...
0 votes
1 answer
331 views

Using st_union for joining overlapped areas in PostGIS?

I have two land use maps for London which have an overlapped area. Can I use the st_union function in postgis to join them together? The attribute table contains information includes legends and ...
2 votes
1 answer
382 views

ogr2ogr sql - returning points from one sqlite dataset that intersect with another

I am having great difficulty with the simple st_intersect command when applied to two datasets containing points, derived from the same source. I am using osgeo4w gdal 2.1.3 at command line. My aim ...
1 vote
1 answer
582 views

get WKT for OSM node, Way and relation for a specific tag

How can I obtain semantically correct WKT geometries for a OSM way or relation or node? Well node is simple and way as well, but I am struggling with the relations which can contain many ways: For ...
2 votes
2 answers
1k views

PostGIS: Calculating the shortest distance between one point and MULTILINESTRING objects

I have a table with MULTILINESTRING objects and I want to build a query that outputs the shortest distances between every MULTILINESTRING object and a specific point (the same for all records) using ...
0 votes
0 answers
64 views

Using SQL Mode in ArcGIS Pro

I am trying to build a SQL expression using SQL Mode in "Make Feature Layer" tool. I have a Land Use/Land Cover Layer that contains several residential classifications (low density, medium density, ...
2 votes
1 answer
4k views

Writing query to select MIN and MAX values by groups?

In ArcMap 10 i'm working in a Personal Geodatabase where the points (see screenshot below) represent the number of each house in the street. Each point has an ObjectID. This ObjectID is an unique ...
2 votes
1 answer
4k views

Using PostGis BBox and ST_intersects Queries? [closed]

CartoDb Recommend here that the following query is faster than an St_intersects Query SELECT * FROM {table_name} WHERE the_geom && ST_SetSRID(ST_MakeBox2D(ST_Point(-73.9980, 40.726), ST_Point(...
2 votes
0 answers
306 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 ...
1 vote
1 answer
553 views

Adding custom constraint to a GeoDjango table?

I wish to add a custom constraint to a GeoDjango table. This is the corresponding sql-statement: ALTER TABLE my_table ADD CONSTRAINT enforce_valid_geom CHECK (ST_IsValid(geom)); Do I really have ...
14 votes
1 answer
6k views

How to use ST_DelaunayTriangles to construct a Voronoi diagram?

(edit 2019) ST_VoronoiPolygons available since PostGIS v2.3! With PostGIS 2.1+ we can use ST_DelaunayTriangles() to generate a Delaunay triangulation, that is a dual graph of its Voronoi diagram, and,...
0 votes
1 answer
58 views

How to get sum of a subset as opposed to the last value? CARTO

When choosing a date subset the displayed value of my application is the lastest value as opposed to the sum on that subset. In the example, 943.974 is the dosage for June 12th. The sum of dosage ...
1 vote
0 answers
109 views

PostGIS ST_Within processing speed

I've created an SQL script that selects a given set of polygons ("v_rag_RenouvellementClaim") that are completeley within a specified buffer ("v_rag_RenouvellementClaimGrtr25000_Buffer") using PostGIS ...
2 votes
1 answer
616 views

Joining a csv file to shp (ogr2ogr)

I've been trying to use a ogr2ogr -sql call to create a ESRI.shp file with the joined attributes - but it keeps on failing due to "ERROR 1 SELECT from table inshape failed, no such table/feature" ...
0 votes
0 answers
81 views

How to determine shifting intersections of polygons using MapInfo SQL?

I'm looking for a Mapinfo SQL which can look at two tables and easily determine if a particular smaller bounded polygon has now moved into a different larger bounded polygon because there has been ...
1 vote
2 answers
497 views

Configuring results from STCentroid select statement?

Is it possible to configure the results from a STCentroid select statement? If I run a select statement at the minute, I will get overly precise responses i.e. POINT (399419.42500005924 90290....
2 votes
2 answers
404 views

SQL Query not selecting all points that it should?

I am working on a map of 60+ monitoring wells and I plotted them through an excel data sheet and a few tools. In that data sheet, I have Benzene concentrations associated with almost each point. I ...
5 votes
1 answer
1k views

Geopackage - select on geometry column returns null

In DB Manager in QGIS 2.18.26 I want to select geometries in geopackage table. The returned geometry column is null but the source layer can be loaded as a geometry table in Qgis and i can read the ...
0 votes
1 answer
196 views

Counting (Multi)Polygons Within (Multi)Polygons on same Table

So I have a table with approximately 10k rows, with each row having its corresponding geometry entry. What I am attempting to do is count how many row geometries are contained/within the geometry of a ...
4 votes
1 answer
3k views

Tutorial for WFS connection SQL query composer

Has someone a link to a tutorial or a manual that explain the functionalities of the 'SQL query composer' for QGIS? I'm working with WFS vectors connected from the same server and I would like to ...
2 votes
0 answers
62 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) ...
0 votes
1 answer
54 views

Is additional SQL syntax necessary to account for a field with a domain/coded values?

I'm attempting to create a spatial view. The following statement returns an empty view, even though there are "Flushed" records in the MAINT_ACTIVITY field. SELECT h.OBJECTID, h.FACILITYID, h....
0 votes
2 answers
83 views

Populate SQL Expression in Toolbox based on hard coded parameter [closed]

I have an ArcGIS toolbox script that applies a user-defined definition query to multiple layers within the current MXD. The only user variable currently required is the definition query itself, which ...
2 votes
2 answers
3k views

Convert RT90 into WGS84

Goal: Gain WGS84 decimal to locate some cities in Google Map. Problem: I don't know how to convert the value of the RT90 coordinate system into WGS84 in SQL Server.
0 votes
0 answers
72 views

Remove tolerance in PostGIS requests

I just read the problems similar to mine on this forum, but no solution helped me. To put it simply, I use ST_Touches in an algo in PHP that allows me to test if two points touch. Here is the query: ...
0 votes
1 answer
140 views

Query execution time

I am new to SQL. I am trying to execute some queries on a database using PostgreSQL with the PostGIS extension. I cannot understand why the execution of the following query takes about 5 mins select ...

1
14 15
16
17 18
37