We’re rewarding the question askers & reputations are being recalculated! Read more.

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 ...
47
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 ...
37
votes
21answers
30k 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-...
33
votes
5answers
19k 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 ( ...
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 ...
20
votes
3answers
26k views

Creating spatial tables with PostGIS

In the PostGIS documentation it says that there are two steps to creating a spatial table with SQL: Create a normal non-spatial table. Add a spatial column to the table using the OpenGIS "...
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?
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 ...
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' ...
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 ...
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 ...
16
votes
1answer
2k views

Identifying topological relationships using PostGIS

I'm trying to perform a basic typology analysis using PostGIS. My objective is to find any polygons that touch other polygons. To do this, I thought the ST_GetFaceEdges would work (reference). I want ...
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 ...
15
votes
3answers
8k views

Intersecting a raster with a polygon using PostGIS - artefact error

I'm using PostGIS2.0 to do some raster/polygon intersections. I'm having difficulty understanding which operation I should use, and what the quickest way of performing this is. My problem is as ...
14
votes
1answer
799 views

Nearest Neighbor Grouped Selection in QGIS

I have a list containing over 100,000 points in lat/long format which I have imported into qgis. Now, what I am trying to do here is group all of these points into box groups and by that I ...
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 = " \"...
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 ...
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, ...
13
votes
2answers
6k views

What are benefits of using ArcGIS with PostgreSQL over simply ArcSDE?

I understand that in order to benefit from ArcGIS spatial datatypes (ST_GEOMETRY, versioned geodatabases etc.) ArcSDE needs to be installed - and with the 10.1 client it is automatically installed (...
13
votes
1answer
728 views

Combined Hstore key/value and spatial query too slow to handle bigger OSM extracts

I'm trying to compute some statistics for OSM data using PostgreSQL 9.3.5 and PostGIS 2.1.4. I started with a small bavaria extract which I downloaded from Geofabrik. The db schema is the normal API 0....
13
votes
1answer
2k views

Create polygon from lines in PostGIS

I'm trying to divide a polygon in smaller polygons from a line, but I think I can't use the st_split function. What I need is to create small polygons inside a big one, using linestring grid. I've ...
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,...
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 ...
12
votes
2answers
12k views

Import large CSV file into PostGIS

I am trying to import CSV files into PostGIS. Following this post, I have created tables before. I found other suggestions saying that I can run the copy command. If I run this command: COPY table ...
12
votes
1answer
4k views

How to trace SQL queries sent by ArcGIS Server (ArcSDE) to Oracle database?

I'd like to generate a log file containing all SQL queries sent by ArcGIS Server (ArcSDE) to Oracle database. Is there a way to do so? I'm using Oracle 11g and ArcGIS Server 10.0 on Windows. ArcSDE is ...
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 ...
12
votes
1answer
201 views

Is the ST_ prefix appropriate for functions not included in SQL/MM Part 3?

I was reading a thread on the geospatial extension for Presto in this Github issue, where a function, line_locate_point, was introduced. It was based off PostGIS's ST_LineLocatePoint function, which ...
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....
11
votes
1answer
2k views

Join based on maximum overlap in PostGIS/PostGresQL?

I have two sets of polygons in two tables. The sets overlap each other. For each polygon in set A, I would like to get the ID of the polygon in set B that it overlaps the most. I'm using PostgreSQL ...
11
votes
2answers
265 views

How to recursively loop through parent polygons intersections to get smallest (child) polygons with no overlaps?

I'm struggling with a problem for a couple of days and realized many people also get stuck when the topic is intersections in PostGIS (v2.5). That's why I decided to ask for a more detailed and ...
11
votes
4answers
6k views

ST_Intersection slow query

I am trying to perform an intersection between two layers: Polyline layer representing some roads (~5500 rows) Polygon layer representing irregularly shaped buffers around various points of interest (...
10
votes
2answers
19k views

Creating geometry from lat/lon in table using PostGIS?

I have a table with over 800,000 records including a lat and long column. The data source did not create geometries, so I was looking around how to do this. I know there's ST_MakePoint(x,y,SRID) but ...
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. ...
10
votes
4answers
2k views

To what extent is SQL's language accessible via ArcGIS tools?

That question just sounds so redundant... At any rate, I have been asked to explore the uses of SQL in ArcGIS. We use an SDE DB for all server-side storage and DB managing/large-scale editing. However,...
10
votes
1answer
2k views

Performance Gain Through GIST Index for Point in Polygon Query

I have two tables: locations(id,region_id,the_geom) and regions(id,the_geom). For each location point I want to determine the region it is located in: UPDATE locations SET region_id = (SELECT id ...
10
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 "...
10
votes
3answers
2k views

Commenting Out Definition Queries in ArcGIS Desktop?

I use Definition Queries a lot in my daily GIS life, but one of the many things I've found annoying about ArcMap is the inability to comment out Definition Queries. I'd like to have this option as ...
10
votes
2answers
673 views

How do I perform a 3 parameter geotransformation and projection in Sql Server 2012?

I have a table with latitude longitude (NAD27) columns in it. I compute two other columns, X and Y, representing Web Mercator (WGS84) location. Currently I'm using a Arcmap to do this, by applying ...
9
votes
2answers
9k views

Select layer by attribute using SQL statement LEN() in ArcMap?

I want to query the length of a string column and thought to use: LEN([ColumnName]) > 5 However, that doesn't work at all. I'm querying a table in a fGDB in ArcMap 10.1, hence the notation of [...
9
votes
1answer
22k views

How to use Select by Attributes query with semicolons?

I am trying to select multiple (50 or more) unique numeric records using ArcGIS 10's "Select by Attributes" using semicolons ';' after each record which it verifies as good to go. Please see screen ...
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 ...
9
votes
2answers
6k views

GDAL SQL syntax to add field an put values

I try to make a new field in my attribute table and put in values from another field after converting from character to integer but can't get the right syntax. I run this as shell-script in MSYS. cd ...
9
votes
5answers
7k views

GIS analysis to find duplicate geometries

I have a big shapefile that contains all the buildings and houses of the town that I work in (approx. 90,000 features). The data of the buildings/houses are saved by the town's surveying engineers and ...
9
votes
2answers
6k views

Find points that lie within a set of coordinates

I have a PostGIS db, and I want to find the points that lie within some region (a bounding box). I have two sets of coordinates, from which I can get all four rectangle points that form the box (if ...
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 ...
9
votes
1answer
1k views

Assign Z value to start & end of PostGIS line

I have a line table in my PostGIS database and a table with invert levels for the start and end of each line. My goal is to create create 3D lines in my table, from my invert data. I know I can use ...
9
votes
0answers
234 views

How do I select 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 ...
8
votes
2answers
13k views

Creating join based on multiple fields using ArcGIS Desktop?

I have two feature classes in a File Geodatabase that I would like to join based on multiple fields. I've searched this site and Google and all I have come up with was to use the Make Query Table ...
8
votes
4answers
3k views

Queries returning very big datasets in PostGIS

I have a PostGIS query that will return several million rows: SELECT t1.id AS id1, t2.id AS id2, ABS(t1.mean_h - t2.mean_h) AS h_diff, ST_Distance(t1.the_geom, t2.the_geom) AS dist FROM ...