Questions tagged [sql]
Acronym for Structured Query Language. A syntax for retrieving and manipulating data from a relational database.
1,837
questions
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 ...