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
62
votes
2answers
12k views

Acquiring ArcGIS-like speed in Postgis

I have been using Postgis 2.0 for 3/4 of a year now and while I really enjoy using it, excessive query processing time has rendered it basically unusable for my use case. I tend to do heavy ...
37
votes
21answers
31k views

Connecting to PostGIS database from ArcMap for display and query without ArcSDE?

How can I connect to a PostGIS database from ArcMap using ArcGIS Desktop 9.3 and later? I would like to be able to perform spatially enabled queries and receive the results back (e.g. spatial and non-...
36
votes
4answers
20k views

SQL query to have a complete geojson feature from PostGIS?

I would like to get a geojson feature with properties from PostGIS. I have found an example to have a feature collection but I can't make it works for just a feature. SELECT row_to_json(fc) FROM ( ...
12
votes
1answer
5k views

How to use ST_DelaunayTriangles to construct a Voronoi diagram?

(edit 2019) ST_VoronoiPolygons available since PostGIS v2.3! With PostGIS 2.1+ we can use ST_DelaunayTriangles() to generate a Delaunay triangulation, that is a dual graph of its Voronoi diagram, and,...
16
votes
3answers
33k views

Converting MultiLinestring to Linestring with PostGIS?

I have a multiline string and want to convert it to a linestring in order to use some functions that only work with linestrings. I used ST_Dump() and got a collection of my geometries. If I merge them ...
13
votes
3answers
15k views

Selecting features by attribute if in Python list?

I am trying to complete a select by attribute in Python but based on the query of whether an attribute is present in a list. Such a query at its simplest should be something like this: qry = " \"...
48
votes
4answers
35k views

Joining lots of small polygons to form larger polygon using PostGIS?

I have the following layer using SRID 27700 in postgis: It's every administrative region in the UK, and (as you can see from the colour grouping) each of them has a text field specifying the county ...
41
votes
8answers
16k views

Select features that do NOT intersect in PostGIS

This seems to me like such a simple question (and it probably is) yet I cannot seem to find an example that gives me the answer. Using PostGIS, I just want to select points that fall outside of ...
17
votes
1answer
10k views

Selecting features by attributes using ogr2ogr?

I am writing a shapefile after some processing; however, in the final step I need to do some selection by attributes from the the shapefile. I am using the command in a shell with the aim of using it ...
11
votes
1answer
8k views

Updating field to give count of points in polygon using STIntersects?

I have a points layer (dbo.ptLayer) Around 1M points Spatial Geometry Type (dbo.ptLayer.geom) No spatial index just yet, but will create one once data gathering complete. I have a polygon layer (dbo....
9
votes
3answers
5k views

Using ST_Difference to remove overlapping features?

I am trying to use ST_Difference to create a set of polygons (processing.trimmedparcelsnew) that do not contain any of the area covered by another set of polygons (test.single_geometry_1) using ...
18
votes
4answers
12k views

Getting list of distinct values from shapefile field using QGIS?

I have a shapefile whose features I can filter by specifying a where clause in the query dialogue. F.x. I have a field named 'city_name' and by stating 'city_name = "London"' in the where clause only ...
4
votes
3answers
1k views

Converting double field to integer in database view so ArcMap query layer can use field as unique identifier

I have a field in a table that is a double data type, but the values are actually integers. The table is from third-party software, so I don't have control of the field data types. I'm attempting to ...
6
votes
2answers
12k views

Selecting max value from group using ArcGIS definition query?

In ArcGIS 10 I am trying to do a Definition Query that will select the records that are the max values from a specific group. Here is what I have tried in the Query Builder but I am getting an "the ...
3
votes
2answers
1k views

ArcObjects: Alternative workflow to using a SQL query with over 1000 values in IN statement

I have a ListBox listing the unique values of a column in a feature class; the user can select any number of values and click a button to then zoom to the set of features matching those values. A ...
22
votes
5answers
12k views

A SQL console in QGIS

I know QGIS comes with its field calculator, which is not bad. It also comes with ftools which permits several operations on geometry and attributes. But we often need more control and I haven't seen ...
10
votes
4answers
13k views

Finding nearest neighbors between two tables with point locations in SpatiaLite?

I have started playing with SpatiaLite today and already stumbled upon a problem. For each point location stored in tableOne I would like to select one, nearest (linear distance) point from tableTwo. ...
12
votes
3answers
3k views

Constructing Voronoi diagram in PostGIS

I'm trying to construct voronoi diagram from grid of points using modified code from here. This is SQL query after my modifications: DROP TABLE IF EXISTS example.voronoi; WITH -- Sample set of ...
11
votes
3answers
6k views

Change polygon “handedness” for SQL 2008 (reverse polygon vertex order)

I have a few hundred shapes (polygons and multipolygons) each consisting of tens of thousands of points that I am trying to get into SQL 2008. Unfortunately, the shapes I have tried to import are "...
13
votes
3answers
3k views

How to interpolate GPS Positions in PostGIS

I have a PostGIS table of GPS positions for every five seconds: 2011-01-01 00:00:05, POINT(x1,y1) 2011-01-01 00:00:10, POINT(x2,y2) 2011-01-01 00:00:15, POINT(x3,y3) ... I'm looking for a query that ...
12
votes
1answer
6k views

SELECT TOP query in ArcGIS Desktop?

Is there a way to use a SELECT TOP (n) within ArcGIS desktop? Is appears that since the ArcGIS query builder is effectively building the SQL statement after the WHERE clause, that this may not be ...
19
votes
4answers
12k views

Bounding box for PostGIS table

Is there an easy way to get the bounding box for an entire table in PostGIS?
7
votes
3answers
39k views

Using String Functions (LEFT, RIGHT, MID) in Select By Attributes against a File Geodatabase using ArcMap 10

I finally (albeit reluctantly) made the switch from a personal geodatabase (pGDB) to a file geodatabse (fGDB) not too long ago. The main reason it took so long to switch has to do with how Select by ...
6
votes
1answer
4k views

What expressions for QGIS field-calculator?

I do not understand what language I have to use in field-calculator and labels on QGIS: SQL or Python? And where I can find the possible script/expressions to use? in the image you can see the ...
9
votes
3answers
5k views

Label points at regular intervals using ArcGIS for Desktop?

I'm working with lines on roads represented by a large number of sequencial points. Since labeling all of them would make the points impossible to identify, I'd like to know if there is a way to label ...
6
votes
3answers
7k views

Merge lines with the same 'name' attribute on QGIS?

I have a shapefile with a few thousand lines, each is a street segment. I'd like to merge all features with the same 'streetName' attribute into a single attribute. I only really care about the name ...
4
votes
2answers
2k views

Using nearest neighbor query in SpatiaLite?

I am trying to find the nearest line to each point in a layer, and I have over 1 million points. I want to be able to associated the closest unique line ID with the point unique IDs. I have tried to ...
4
votes
1answer
2k views

Cartodb - SQL query of public visualization of a private table

The team at CartoDB have recently(last few days) made some useful updates that now allow public visualisation maps to reference data in private tables. The change has only been made in the last day ...
3
votes
1answer
2k views

Selecting top 5 most expensive items in personal geodatabase

I'd like to know what is the best way to select the 5 most expensive items (in terms of actual monetary value) in my personal geodatabase where i have a price field. Prices are not necessarily ordered ...
1
vote
1answer
1k views

Using PostGis BBox and ST_intersects Queries? [closed]

CartoDb Recommend here that the following query is faster than an St_intersects Query SELECT * FROM {table_name} WHERE the_geom && ST_SetSRID(ST_MakeBox2D(ST_Point(-73.9980, 40.726), ST_Point(...
1
vote
2answers
318 views

Using SQL query new feature with loop in ArcPy?

I'm trying to create an individual buffer feature class for each column of an input feature class, where there is a field that contains the buffer distance. I think there is a problem with my SQL ...
18
votes
5answers
25k views

Selecting contours which heights are integers?

I have a Contours Featureclass, with an interval of 0.2 meters. I wish to select only those Contours, which are at 1 m interval? I have tried using the following syntax in the 'Select by Attribute' ...
15
votes
3answers
4k views

Join intersecting lines with PostGIS

This is something that I'm almost ashamed to ask but I can't seem to get it to work for the life of me. I have a road layer with segments, each segment has a Road ID and a segment type. I would like ...
13
votes
2answers
7k views

Getting geometry from multiple tables using PostGIS?

I am very new to PostGIS, PostgreSQL, and SQL in general. I have 44 tables in my PostGIS database, and each one represents a different layer of vector data. Each was loaded from a separate shapefile, ...
3
votes
3answers
3k views

How to Calculate length of polyline geometry for several tables in PostGIS?

PostGIS database contains several geometry (polyline) tables (with names "D1_r", "D2_r", "D3_r"). I calculate length for one "postgis layer" use request: SELECT sum(ST_Length_Spheroid(the_geom,'...
7
votes
1answer
645 views

Get ArcMap to recognize ST_POINT (SDE.ST_GEOMETRY)

I have a sde.st_geometry line feature class called ROADS. I've created a view using a st_geometry function called st_startpoint which returns an st_point column. st_point is a subclass of st_geometry; ...
5
votes
1answer
3k views

Selection by attribute string with wildcard as part of name in ArcGIS Desktop?

I have an attribute field in my file geodatabase (GDB) where I'm trying to select all records that contain an underbar in the string field. The issue is that the underbar is considered a SQL wildcard ...
4
votes
1answer
2k views

SQL queries in shapefiles using python/gdal return none result

I want try use Python and GDAL to create some SQL queries in my shapefiles. I try something and it looks good, but doesn't work. I get a none result. Here is the code : from osgeo import ogr ogr_ds =...
4
votes
2answers
3k views

Writing SQL WHERE Clause using Multiple Choice values in FME?

I published a parameter which is of Choice Multiple type. I wish to use values from this parameter for WHERE Clause when Reading Inputed Data in the begin of Workbench. So there is no problem if I ...
3
votes
1answer
1k views

Convert SqlGeometry data from SQL directly to Tiles / PNG file

I would like to know a basic method for 'exporting' SqlGeometry data from SQL Server 2008 to a Tile Server / PNG File generator. I already have a Silverlight Map application which uses a Custom Tile ...
3
votes
3answers
1k views

List of all fields and their domains, for each table using SQL (table_name, field_name, domain_name)

I'm trying to list all fields (in all tables in an Oracle geodatabase) and the cv domains that they use. +------------+------------+-------------+ | table_name | field_name | domain_name | +----------...
3
votes
1answer
463 views

Creating SpatiaLite View in QGIS?

Using these instructions (there are several other examples to find, all look the same and all of them seem to work) http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/sp-view.html "Hand-writing ...
3
votes
2answers
13k views

Using QGIS to Select by Expression?

A basic QGIS 2.2.0-Valmiera, Select By Expression - Field Calculator question. Introduction I am a geologist searching through a geological database (see attached figure). I have a shapefile and ...
2
votes
1answer
841 views

Get list of all tables in CartoDB account with SQL statement?

How can I get a list of all available tables in my own authenticated CartoDB account with a SQL statement? This returns null: SELECT table_name as t FROM information_schema.tables WHERE table_schema ...
2
votes
1answer
51 views

PostgreSQL create line from points

How do i create a linestring from points sort by date in PostgreSQL? I tried: SELECT observations.id, ST_MakeLine(observations.geom ORDER BY observations.date) AS newgeom FROM observations ...
1
vote
1answer
86 views

MultiValue-String to SQL Expressions

I am aware that problems like mine exist (example) but I think, this is slightly different. I want to use MultiValue to let the user choose various inputs at the same time. The problem is, that it's ...
5
votes
1answer
4k views

Performing Geoprocessing SQL Queries in QGIS from a PostGIS Database

This may be a dumb question, but is there way to run a PostGIS geoprocessing SQL query in QGIS? Specifically, I am thinking of running SQLs such as ST_Union, ST_Split etc etc. I have tried the ...
4
votes
1answer
995 views

Populating SQL options for parameter in ArcGIS tool?

In ArcGIS Desktop 10 I am trying to create a tool where individuals can select a variety of records using a SQL expression that is then passed to a calculation. I can add a parameter under the tool, ...
3
votes
2answers
543 views

Fill with @row_number based on group field in QGIS 3?

Is there a way to fill the table with a row number so that count will restart as new group (identified by a separate column) starts? As a result, "row number" column will restart the count when the ...
2
votes
1answer
407 views

arcpy.da.UpdateCursor with sql_clause for artifically inverting cursor loops

Why is row not recognized within my update cursor? The data is stored in a file geodatabase. I am using ArcGIS Desktop version 10.1 ArcInfo License. The screen shot shows the data "ORDER BY X ASC" ...