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
0
votes
1answer
54 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
34 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
51 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
37 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
133 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
93 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
58 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
54 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
346 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
87 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
88 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
29 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
36 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
75 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
166 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
44 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
72 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
60 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
49 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
59 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
25 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
32 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
139 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
30 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
53 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
67 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
40 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
24 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
27 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
182 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
86 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
22 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
40 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
34 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
35 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
35 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
29 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
71 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
48 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 ...
2
votes
0answers
44 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
36 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
71 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 (...
1
vote
1answer
65 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 ...
3
votes
1answer
83 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
265 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
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
47 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 ...