Questions tagged [sql]

Acronym for Structured Query Language. A syntax for retrieving and manipulating data from a relational database.

Filter by
Sorted by
Tagged with
2
votes
1answer
54 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
59 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
48 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 ...
5
votes
1answer
317 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 ...
1
vote
1answer
52 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
86 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
140 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
137 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' ...
1
vote
0answers
51 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
433 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
78 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
273 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') ...
1
vote
1answer
170 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 ...
2
votes
1answer
383 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
40 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
120 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
366 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
28 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
290 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
40 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 ...
2
votes
1answer
127 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
1answer
83 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
33 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
64 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
22 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
90 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
34 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
167 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
59 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
1answer
159 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 ...
1
vote
0answers
94 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
70 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
193 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
101 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
82 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
38 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
166 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
193 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 ...
0
votes
0answers
224 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
352 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 | ...
1
vote
2answers
96 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 ...
2
votes
1answer
183 views

Select layer by location in PostgreSQL

I have a footprint of some buildings (see the picture). The selected buildings have an area below 30 sqm. As you can in the picture see some buildings share almost borders with a building bigger ...
3
votes
1answer
236 views

What is the metric of the radius PostGIS query?

I am trying to make a query (using raw query in sequelize orm for a PostgreSQL with PostGIS database) for a radius of a certain 'maxDistance' radius (preferably in meters): const maxDistance = ...
-2
votes
1answer
62 views

Select the parts of a line that are within an area in PostGIS [closed]

In my project I have a road network and many administrative boundaries. My goal is to select the roads that intersect a concrete district but extracting only the parts of the roads that are within ...
3
votes
1answer
262 views

Clip all layers of a GeoPackage (GPKG) in one step

I would like to clip all layers of a GPKG-File to a given extend (canvas, mask layer, rectangle...) in one step. I could use clipping in batch mode but isn't there a nicer solution doing this in one ...
1
vote
1answer
56 views

Transform CRS for all geometry objects in SQL-Database in one step [closed]

I would like to easily change the CRS for all my geometry objects in a PostGIS/SpaitaLite/GPKG database in one step. In my case, all objects are in EPSG:4326 and I want them in EPSG:3044
1
vote
0answers
97 views

PostgreSQL Views with geometry on QGIS

Not entirely sure what kind of rules does QGIS need to consume SQL views So I've had some success in using an id column and a geometry column but now that I'm doing something a bit more complex, it's ...
1
vote
1answer
79 views

Run query between PostGIS layer and Shapefile layer with DB Manager

Is it possible to use the DB Manager in QGIS (I am using version 3.4) to run a SQL query between a project layer (e.g. shapefile) and a PostGIS layer? For example, I have a polygon shapefile in my ...
0
votes
1answer
267 views

Create Database View with ST_Within Query

I see several other questions similar but none that apply directly to mine. I am using ArcGIS Desktop 10.6.1 with SQL Server running my Enterprise Geodatabase. I have a very large point feature ...
0
votes
1answer
212 views

Querying geometry in perimeter or square with SpatiaLite?

How can I query geometry in a perimeter or a square with SpatiaLite? CREATE TABLE points (id INTEGER,geom GEOMETRY) insert into points values (897, GeomFromText('POINT(40.0 3.0)')) I've tried ...