Questions tagged [sql]
Acronym for Structured Query Language. A syntax for retrieving and manipulating data from a relational database.
1
vote
0answers
12 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 ...
0
votes
1answer
29 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
45 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
13 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
25 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 ...
1
vote
1answer
31 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
62 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
45 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
34 views
Populate SQL Expression in Toolbox based on hard coded parameter
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
22 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
...
2
votes
1answer
42 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
45 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
64 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
77 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
38 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
19 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
66 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
37 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 ...
1
vote
2answers
46 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
18 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
61 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
16 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
21 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....
1
vote
0answers
68 views
Converting Multipolygon to Polygon and saving in new table in Oracle Spatial?
I have a data set with mixed SDO_GTYPE 2007 and SDO_GTYPE 2003, however for the task I am working on only SDO_GTYPE 2003 is allowed for practical reasons.
I was able to extract the polygons in a ...
1
vote
1answer
22 views
Select layer by attribute - SQL command pending on two columns
I have a layer, with two columns called NAME and TYPE.
A polygon in the layer has only one NAME, but can contain multiple TYPE of vegetation.
I want to select different Types of vegetation, where the ...
0
votes
1answer
63 views
Is it possible to use SELECT ST_GeomFromText( with other SELECT? [closed]
I'm trying to use SELECT ST_GeomFromText() with a value catched from another query:
SELECT ST_GeomFromText(
ST_AsText(
ST_GeomFromGeoJSON(
SELECT
column-name
FROM table-name
ORDER BY ...
1
vote
1answer
35 views
Filter PostGIS DWithin center point SQL
Is there a way I can remove the center point from my results in a query like the following?
(select * from cities
where l = true
and ST_DWithin(geography(cities.geog), (SELECT geog FROM cities WHERE ...
1
vote
1answer
19 views
Add FID to attribute table using ogrinfo
I want to add the fid to the attribute table using ogrinfo in OSGeo4W Shell.
Here is my code:
ogrinfo layer.shp -sql "ALTER TABLE layer ADD COLUMN ID integer"
ogrinfo layer.shp -dialect SQLite -...
1
vote
0answers
21 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 ...
1
vote
0answers
28 views
What syntax should I use in my SQL query in the select statement in ArcMap if I want to use two In-Line substitution variables
I am trying to iterate though features in a shapefile. There are two columns I need to iterate through. First, I need to iterate through company names, and then using another iterator in a separate ...
0
votes
0answers
40 views
Select Analysis Invalid Expression
I am attempting to select nulls from an in_memory feature layer using Arcpy's Select_analysis. Code example below, as well as, various iterations of attempts to alter the SQL query with no success.
...
0
votes
0answers
21 views
Incorrect SQL syntax [duplicate]
I'm trying to execute the following code and it throws me an error.
value=u'L_PARCEL_A'
where_clause="'LAYER'='{0}'".format(str(value))
arcpy.Select_analysis("input_lu1",stat_feature,where_clause)
...
0
votes
1answer
33 views
Selecting records by SQL Query and saving SUM or COUNT result into differnt table
I am trying to create a python toolbox with the following function:
I want to select points or lines within a polygon and save the selected feautres as a temporarily Feature. (That works)
Then I want ...
0
votes
0answers
24 views
ArcGIS Feature Layer's Definition query to change by scale
I have a feature layer of road polylines. I would like to select the type of roads displayed on my client as a result of scale level.
For example:
At scale 100000 I would like to return only roads ...
2
votes
0answers
37 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 ▼)
1
vote
1answer
50 views
Different result with Query builder and the processing 'extract by expression'
I have a attribute table (xlsx sheet imported with the plugin 'spreadsheet layer'). For example, it contain only 15 lines
I have filtred the data with this request that return 6 lines (correct result)...
1
vote
2answers
128 views
Using PostGIS to find which county in California has most settlements in forest?
Using PostGIS how do I find which county in California has the most settlements in the forest?
I have point settlements settlements_CA, polygon forests forests_CA and polygon counties counties_CA in ...
0
votes
0answers
33 views
Speeding up Oracle Spatial intersection query and indexes
I have about 300 000 records (worksites) in a table (S) with id (id_s), start_date, end_date (and in some cases an authorisation date - AUTH_START).
I have a second table containing the id of 500 of ...
1
vote
0answers
52 views
AGOL & SQL database: Is ArcGIS for Server necessary?
A client is requesting we come up with a Python script to have an AGOL feature layer and a SQL Server database containing similar information be able to read/write to each other without having to use ...
1
vote
2answers
50 views
How to delete boundary lines of split shapefile tiles with SQL of Virtual Layer
I have the split shapefiles like same size tiles.
I want to show these merged on QGIS.
So, I tried to use Virtual Layer like following image.
And, I wrote following SQL:
select * from north
UNION
...
1
vote
1answer
64 views
Execute Oracle stored procedure from ArcSDESQLExecute
I'm attempting to execute an Oracle 12c stored procedure using ArcSDESQLExecute:
import arcpy
egdb_conn = arcpy.ArcSDESQLExecute(r"Database Connections\con1.sde")
sql = '''execute update_sle (...
2
votes
2answers
78 views
PostGIS intersects and case function
Hi i've been setting up a script to assign values depending on one type of polygon intersecting with another polygon. I have two feature tables - one with building footprints and one with polygons ...
0
votes
1answer
33 views
SQL expression to select specific month across years
What is the ArcGIS SQL expression to select all rows containing the month of May across several years?
Desired records highlighted in Excel:
The source table is in a file geodatabase and I'm using ...
0
votes
1answer
35 views
Using an SQL Query to skip or run a code in ArcPy/ Python
Using ArcMap 10.4 and Python I am trying to run a code (arcpy.ExportReport) on a bunch of different frequency tables. Which I have completed the only problem is that if I have a table that does not ...
3
votes
1answer
76 views
Extending a segment in QGIS to check intersection with a polygon
I am trying to find the adress points (red dots) located in front of an obstacle (red polygon). The street is green and the pink line represents the street face (Si).
This is what I have so far, but ...
0
votes
1answer
48 views
Altering SQL View GeoServer from application
I am making an application and I want to access a lot of weather data.
Now I want to use the SQL View option to let the WMS layer know which timestamp to use and then it shows me all the data from ...
2
votes
2answers
77 views
Calculating point layer values aggregated by sub-polygons in QGIS 2
I simply have
a polygon layer BereichBerechnung with a field "Are_Number"
a point layer EW2017 with a field "EWjeAdr"
In QGIS with a Virtual Layer, I want to calculate the sum of the field "EWjeAdr" ...
0
votes
0answers
107 views
ArcGIS vs QGIS vs PostGIS projection issue
Windows 10, ArcGIS Pro, QGIS 2.18, pgAdmin 4-postgreSQL
I have two (sample area) PostGIS tables in a PostgreSQL database:
building_polys (SRID: 27700) -OS MastermapTopo
address_points (SRID: 4258) -...
1
vote
1answer
74 views
Efficient intersect query for virtual layers in QGIS
I have 3 tables/shapes as follows:
ADRR (adress points)
ADRR_ID | ST_ID (street surface id) | point geometry
STREET
ST_ID | ST_NAME (street name) | polygon geometry
Obstacles
ID_OBS | ...
0
votes
2answers
64 views
Automating Select by location using ArcPy and where_clause?
I have used the code in Coding python loop to select features of polygon layer than are completely contained in group of 11 layers using ArcPy? to automate select by location analysis using ArcPy.
I ...
