Questions tagged [sql]
Acronym for Structured Query Language. A syntax for retrieving and manipulating data from a relational database.
0
votes
1answer
35 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
29 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
23 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
10 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
0answers
27 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
9 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
18 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
25 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
45 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
19 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
42 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
50 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
31 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
20 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
25 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
66 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
45 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
386 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
13 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
26 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....
2
votes
0answers
32 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
0answers
22 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
1answer
55 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 ...
0
votes
0answers
32 views
Selecting max values using Dissolve + keeping other fields?
I have a large feature class table with wind measurements from weather stations. It has the following fields:
Station ID field (repeated hundreds of times)
Measurement result
Date of the measurement
...
1
vote
0answers
23 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 ...
1
vote
1answer
32 views
Solve Esri Toolbox produces empty Shapefiles (make route layer)
I am making a Toolbox for finding the best Pizzaroute between 7 Pizzastops.
We used the make.routelayer from ArcPy. Our Pizzaroutetime expressed not our expression, rather all Firestations (station 1-...
1
vote
1answer
54 views
Efficiently query two properties QGIS spatialite
I have a simple query that involves two steps:
non-spatial criteria (should be super-fast)
spatial criteria (which is really slow)
I would like to run the spatial query only on the filtered layer (...
0
votes
0answers
17 views
Extract intersecting polygon from multipolygon search
I'm using the latest SQL Server and trying to build a query where i can search a database of spatial data and find the intersecting polygon.
The problem is that the spatial data can be of ...
1
vote
1answer
27 views
Prioritising values in accordance to various Columns
I previously asked for help in Prioritizing the Attribute table of a self made polygon layer
Prioritising polygons in QGIS
Now I want to change this prioritisation in accordance to multiple values ...
2
votes
1answer
45 views
Add virtual layer to aggregate feature
newbie here of QGIS here, I was trying to aggregate a feature from a CSV with no geometries. The query is very basic:
SELECT
"PROV",
SUM("POP_2012") AS POP_2012
FROM "municipalities-tuscany"
...
1
vote
1answer
64 views
PostgreSQL PostGIS: append table to another table [closed]
I have a large polygon table (30+million rows) and a smaller polygon table (45,000 rows). Both are PostGIS enabled.
The smaller table only contains polygons that do not exist in the large table. Both ...
2
votes
1answer
46 views
Buffer view that calculates numbers from a different layer
Basically people draw lines and from these lines, I need to have a view that will create a buffer that will calculate potential revenue from those lines
mylinetable is a table with a portfolio_id ...
0
votes
2answers
47 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 ...
0
votes
1answer
27 views
ArcPy - Select Layer by Attribute / Delete Selected Layer - SQL
I feel like I've tried so many iterations of this, can anyone assist with how to use ArcPy to selected layer by attribute then delete selected layer? I think my SQL query is incorrect?
import arcpy
...
4
votes
2answers
200 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
1answer
46 views
Problem with if-statement and whereClause format
I have a program which should count the number of a feature class or the length of a feature class depending on the geometry of the input feature class. This part is working.
There are two parameter-...
1
vote
3answers
75 views
How to find the closest polygons to a point
I am trying to the find the four closest building polygons to a junction point layer. I understand how to obtain this for a single point but am unsure of how to specify the four closest polygons for ...
4
votes
1answer
101 views
Select all columns except geometry using virtual layers
Is it possible to select all columns except geometry using virtual layers in QGIS?
For example I have this (simplified) query:
Select *, st_buffer(geometry, 100) From mypolygons
It does not work. ...
1
vote
1answer
53 views
PostGIS SQL Query Filter by Bounding Box
I have the following query on a PostGIS Database
SELECT row_to_json(fc)
FROM (
SELECT 'FeatureCollection' As type,
array_to_json(array_agg(f)) As features
FROM (
SELECT 'Feature' ...
0
votes
0answers
28 views
ArcGIS Pro Display Filters not working?
I love the new Display Filters feature in ArcGIS Pro. I've used them a few times and they're great! However, recently when I've tried to utilize them they don't seem to work anymore. Below is a ...
1
vote
1answer
133 views
Adding a column and update values using SQL in QGIS
I have a vector file "data.shp" containing the following fields:
ID, value1, AREA
I want to add a new column "value2" and set it as value1/AREA. Since I want to do that for many columns and files I ...
1
vote
1answer
47 views
If Statements in ArcPy Select Feature SQL Query
I'm trying to select pipes with specific codes. These codes are 8 digits long and I only need to select certain combinations. For example, pipes that have codes with the number 8 in the 7th position ...
2
votes
2answers
65 views
Adding field in vector layer from attribute table in QGIS? [duplicate]
I have an Attribute Table named 'data.shp' and a Vector Layer 'pop.shp'.
The 'data.shp' has the following fields "ID", "value1", "value2"
The 'pop.shp' has the "ID" field (same with the 'data.shp')
...
0
votes
0answers
49 views
How to remove artifacts with SQL Server GEOMETRTY::UnionAggregate()
We'd like to do an effective "real-time dissolve" on an Esri feature class with a SQL GEOMETRY shape where a parcel layer is "dissolved" into products using an attribute (ProductID) of the parcel ...
1
vote
1answer
134 views
Create geometry column in Geopackage with SQL
How to create a table with geometry column that is recognized as spatial layer? I use the DB manager from QGIS 3.4.2
I use the following:
CREATE TABLE 'sample_attribute_with_geometry' (
id INTEGER ...
0
votes
0answers
22 views
Apply gdal_rasterize with spatilite
I am trying to rasterize a spatialite database using gdal_rasterize. I am using gdalUtils in R to do so and have run the following
gdal_rasterize -burn 1 -sql SELECT * FROM plants WHERE binomial="...
2
votes
0answers
58 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....
