Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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 ...