Questions tagged [sql]
Acronym for Structured Query Language. A syntax for retrieving and manipulating data from a relational database.
1,237
questions
1
vote
1answer
51 views
Seeking PyQGIS equivalent to Geopandas or SQL?
Using sql
"DBpop2016" = maximum("DBpop2016" , group_by:= "DAUID")
OR geopandas
print (data.groupby('DAUID') ['DBUID','DBpop2016'].max())
I can select the single DB (of which there are several) ...
0
votes
0answers
25 views
Filter and search large WFS in QGIS
I'm using a large WFS layer that should be searched in such a way that it's filtered by three columns before loading into QGIS.
I know that QGIS has a filter option, but I'm wondering if there is a ...
1
vote
1answer
35 views
Changing SQL syntax in QGIS Virtual Layers
I am experiencing difficulties with SQL syntax in a Virtual Layer. I can do the simplest query form such as:
SELECT *
FROM mytable
and this displays on my canvas.
However, if I try to name fields ...
1
vote
2answers
21 views
Wrong result using virtual layer sum with join and group by
I am trying to join the grouped sum of two layers using virutal layers in QGIS 3.2.
My query looks like:
select nvewi.zone as "Zone-Type", sum(nvewi.bev) as "Inhabitants Isochrones", sum(nvewp.bev) ...
0
votes
1answer
46 views
PostGIS- convert points to polygon based on multiple shared attributes
I have a PostgreSQL PostGIS point table (not multi-points). I want to convert the points to polygons (using convex-hull?) grouping the points by shared attributes from 2 columns.
In this example, '...
3
votes
1answer
43 views
Select the smallest value from a selection using ArcMap SQL selection
I am trying to select the smallest Directionality value based on a selection of two Workingspace values:
In the above picture, there are two "Jeffery's" selected, I want to then select the Jeffery ...
0
votes
1answer
26 views
Quering out multiple values in MapInfo SQL Query
I am trying to filter out around 1000 rows and delete them in MapInfo as they are no longer required in the data set. If there was a smaller quantity then I would use an SQL Query but it has its ...
1
vote
0answers
33 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
0answers
12 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 ...
2
votes
1answer
51 views
QGIS Virtual Layer Join SQLite/ Spatialite
I am attempting to recreate a flowmap workflow from @underdark
The first step involves joining a non-spatial edges layer to a spatial nodes layer:
SELECT StartID, DestID, Weight,
make_line(a....
0
votes
0answers
11 views
Dynamic SQL Expression Parameter in Model Builder tool (Arcmap 10.3)
I have a working model in Arcmap 10.3 that creates a feature class data from CSV file, queries the data according to a SQL Expression, and then conducts many spatial joins.
The model works as ...
0
votes
0answers
21 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: ...
1
vote
0answers
40 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 ...
3
votes
0answers
51 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 ...
0
votes
1answer
50 views
How to fix this Heatmap script from failing SQL expression (integer/string?) using arcpy (ArcMap 10.5)?
I have a heatmap script that will eventually print 10 PDF maps. At the moment it is failing at the SQL expression in the Make Feature Layer right before Kernel Density Raster creation because the ...
1
vote
0answers
31 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
0answers
36 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
1answer
25 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
...
1
vote
1answer
53 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
1answer
47 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 ...
0
votes
1answer
41 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 ...
2
votes
2answers
35 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
1answer
71 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'...
3
votes
1answer
73 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 ...
2
votes
1answer
45 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
1answer
25 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.
1
vote
0answers
26 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, ...
0
votes
1answer
47 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
1answer
44 views
How to calculate extreme point (most north or east etc) of geography(polygon) type in PostGIS
I have a table which include geography(polygon) type.
CREATE TABLE place (
name varchar(100),
geom GEOMETRY(POLYGON, 4326)
);
insert into place
SELECT 'place1', ST_GeomFromText('POLYGON((-71....
1
vote
1answer
34 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 ...
2
votes
1answer
50 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
0answers
16 views
MySQL update geometry field
I have MySQL database with table in
X varchar(50) latin1_swedish_ci No None Change Change Drop Drop
More More
11 Y varchar(50) latin1_swedish_ci No None ...
1
vote
1answer
44 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 ...
0
votes
0answers
25 views
Spatial intersection of features (Oracle Spatial) with a line stored in a local gml file?
Interesting sites are stored as polygon features are stored in an Oracle Spatial read only dB.
I have a local GML file containing a line (EPSG:31370 - corresponding to a trajectory over a couple of ...
0
votes
0answers
21 views
How to update geometry in PostGIS for features that are in an ArcGIS enterprise geodatabase (SDE)?
UpdateGeometrySRID is not working for some reason, I think this may be due to it being an enterprise geodatabase? I also tried the manual version of UpdateGeometrySRID, where you alter the table and ...
0
votes
0answers
30 views
Complete a table automatically
I have a layer containing an id and a geometry.
then I have 3 tables X, Y, Z connected by a relation of 1 to several.
I will want to recover in Z for each new tuple the corresponding geometry,
so as ...
2
votes
2answers
64 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
0answers
22 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
0answers
44 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 ...
4
votes
1answer
62 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)...
1
vote
0answers
34 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 ...
0
votes
1answer
23 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 ...
0
votes
0answers
26 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 ...
3
votes
1answer
71 views
How to create a virtual layer in QGIS that shows only the features in the extent
I am trying to make a virtual layer that is based on a map layer that only returns the features in the current map canvas extent. The purpose is to setup a Data Graph that shows statistical data of ...
0
votes
1answer
58 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"
...
2
votes
2answers
387 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 ...
0
votes
1answer
16 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 ...
0
votes
1answer
31 views
SQL wildcard for Field Name?
I'm wondering if there's anyway to use a wildcard character in a field name. Like using ["Species_%" = 'Hemlock'] instead of ["Species_1" = 'Hemlock' OR "Species_2" = 'Hemlock' ...].
I tried the way ...
0
votes
0answers
31 views
SQL expression for “current” layer when using iterator?
I am trying to run a model to select the maximum (wind measurement) value from each weather station (called SNXXXXX), which has their own feature class in my input gdb:
in the Feature class to ...
0
votes
1answer
33 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....
