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
64
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 ...
42
votes
4answers
27k 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 ( ...
37
votes
21answers
32k 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-...
14
votes
1answer
6k 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,...
20
votes
1answer
13k 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 ...
19
votes
4answers
40k 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 ...
14
votes
4answers
17k 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
40k 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 ...
45
votes
9answers
19k views

Selecting features that do NOT intersect in PostGIS

Using PostGIS, I just want to select points that fall outside of polygons. Ultimately this is the inverse of the ST_Intersects, as far as I can see it. Example: I have a taxlot layer and a address ...
20
votes
5answers
17k 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 ...
5
votes
1answer
901 views

Automatically Snapping Points to Closest Part of Line

I have some line points representing manholes. I also have line features representing roads. Both of these features are currently living in a PostGIS database. I'd like to automatically move these ...
11
votes
4answers
15k 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. ...
11
votes
3answers
7k 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 ...
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 ...
4
votes
3answers
2k 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
13k 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 ...
8
votes
3answers
10k 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 ...
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
13k 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 ...
14
votes
3answers
7k 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 "...
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
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....
12
votes
1answer
7k 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 ...
23
votes
4answers
15k views

Bounding box for PostGIS table

Is there an easy way to get the bounding box for an entire table in PostGIS?
8
votes
4answers
43k 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 ...
3
votes
4answers
3k 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 coded value domains that they use. +------------+------------+-------------+ | table_name | field_name | domain_name | +-...
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 ...
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
2answers
362 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 ...
1
vote
1answer
47 views

PostGIS: ST_ClosestPoint join error

I have two tables: one with geom points of species and an other one with height contour lines geoms. My Idea was to find per specie geom the closest point to a contour 'geom' line and then select its ...
1
vote
1answer
2k 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(...
15
votes
3answers
5k 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 ...
18
votes
5answers
27k 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' ...
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, ...
5
votes
1answer
1k 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 ...
2
votes
1answer
774 views

Speeding Up pgr_dijkstra using bounding box in PostGis2.0

I'm using PostGIS for the first time to calculate distances between two points, for a driving school booking system I am working on. I have loaded in all of the maps for Australia, but the query I ...
7
votes
2answers
988 views

Get ArcGIS to recognize ST_POINT

I have a SDE.ST_GEOMETRY line feature class. I've created a view on it that returns an st_point column. SELECT OBJECTID, SDE.ST_STARTPOINT(SHAPE) AS START_POINT FROM ENG.ROAD There is a known Oracle ...
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,'...
10
votes
1answer
960 views

Selecting the longest connected lines from PostGIS ST_ApproximateMedialAxis

I used PostGIS ST_ApproximateMedialAxis to transform polygons representing mountain ranges into a series of lines. Now, I'd like to select only the "longest" line segment for each group, so that I ...
7
votes
3answers
517 views

Select all polygons that are completely within a circular buffer

I made several buffers around some points. Now I want to select all polygons that are completely within this buffer. My problem is that I also get the polygons that are partially outside of the buffer....
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
571 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 ...
4
votes
1answer
3k 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
3answers
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
2answers
14k 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
374 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 ...
2
votes
1answer
961 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 ...