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

learn more… | top users | synonyms

29
votes
2answers
3k 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 ...
24
votes
9answers
12k views

How to connect to PostGIS database from ArcMap?

How can I connect to a PostGIS database from ArcMap? Solutions for ArcGIS 9.3, 10.0 and later are acceptable. Ideally, I would like to be able to perform spatially enabled queries and receive the ...
5
votes
3answers
2k views

Select features by attribute if in Python list

I believe this will amount to largely a sql syntax question. Essentially, 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. ...
2
votes
2answers
674 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 ...
14
votes
6answers
3k 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 ...
7
votes
2answers
3k views

SELECT TOP query in ArcGIS

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 ...
1
vote
2answers
813 views

How to select all points within a circle in PostGIS?

I have set of points in a PostGIS Table (Type Geometry). Now, I need to filter points that lie in a circle with a center (C) and radius (R)! I expect C to be of type Point or Coordinates.. R to be ...
7
votes
3answers
2k 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 ...
2
votes
1answer
320 views

Select top 5 most items in a 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 geodatabase where i have a price field. Prices are not necessarily ordered but can ...
10
votes
3answers
1k 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 ...
5
votes
3answers
7k views

How to convert MultiLinestring to Linestring?

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 ...
3
votes
3answers
827 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 ...
4
votes
1answer
1k views

STIntersects - Update field to give me count of points in polygon

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 ...
2
votes
1answer
264 views

Raster as Attribute Field Error

I'm using Arc 10.1. I am creating a parcel inventory where I would like to have a photo of the property attached as an attribute and view-able with an HTML popup. I have a polygon feature class built ...
4
votes
1answer
291 views

How to populate SQL options for parameter in ArcGIS 10 tool?

I'm 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, and select SQL ...
1
vote
1answer
163 views

Why does this SQL syntax produce empty feature class with ArcGIS Select_analysis?

I'm working on a script that involves an arcpy.select_analysis function. The problem I have is that when I run the script it produces an empty file. I've verified that the whole thing works when in ...
1
vote
1answer
765 views

Labeling classes for small roads on large extent maps in arcmap with sql statement

I have many small sub divisions that won't label nice, (lot of overlapping) unreadable. While using the bullet leader symbol. What would be the best statement that would place the labels in a clear ...
13
votes
1answer
955 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 ...
11
votes
1answer
548 views

Nearest Neighbor Grouped Selection in QGIS

OK - so I have an interesting problem I am trying to solve. 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 ...
12
votes
5answers
5k views

Use arcgis with SQL Server 2008 as a backend

I'm wondering how best to use arcgis] and sql 2008 together. At the moment we run manual imports and exports between sql 2008 and arcgis 10 shape files. With sql 2008 having a geography data type I ...
8
votes
4answers
1k 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 ...
7
votes
1answer
723 views

PostGIS Intersection and Summarise Attributes

I am usually an ArcGIS desktop user, but keen to start using PostGIS more and I have a really big bit of processing to do. Not sure what functions to use, hopefully someone can help. I have a polygon ...
6
votes
3answers
1k 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 ...
5
votes
4answers
3k 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 ...
14
votes
4answers
4k views

What is the best way to join lots of small polygons to form a larger polygon?

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 ...
9
votes
3answers
2k views

Beginner PostGIS question: getting geometry from multiple tables

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, ...
2
votes
3answers
2k views

Trigger on SQL Server 2008 ArcSDE 10 and ArcGIS Server 10

I need to build a sql server db trigger to update a date field with the current date when a record is inserted or updated, I think. I will be using the date field as a way to recognize ...
8
votes
1answer
798 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 ...
7
votes
2answers
5k 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 ...
6
votes
1answer
343 views

Clarification of Logical Operators in a PostGIS Database

The following query returns this result: select count(*) from a , b where a.geom && b.geom; count ------- 66514 On a postgis 2 database. What does the logical operator AND ...
6
votes
1answer
1k 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 ...
6
votes
1answer
813 views

Spatial joins in OGR SQL

I have seen that OGR supports it's own form of SQL, I was wondering if it's possible to do spatial joins. Something like select all objects where one intersects the other. I did have a look around ...
5
votes
1answer
377 views

How to Comment Out Definition Queries

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 ...
5
votes
2answers
4k views

ARCGIS 10 definition query to select the max value from a group

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
1answer
1k 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 ...
2
votes
1answer
89 views

How to split polygons with another polygon and inheritage data?

Let's say that we have two tables with polygons representing areas. Table A and Table B. Geometries from table A represents both the valid and invalid areas. And the geometries in table B represents ...
1
vote
1answer
188 views

Multiple input shapefiles for ogr2ogr sqlite spatial operations

I'm running up against an issue doing a spatial operation requiring multiple input shapefiles using ogr2ogr and sqlite. I have: ogr2ogr -f "ESRI Shapefile" output.shp input.shp -dialect sqlite -sql ...
-1
votes
1answer
60 views

How to Label Field With Multiple Selected Values?

I have a shape named "Subdistrict" and it shown on Attribut Table like this : No | subd | village 1 | town1 | v1 2 | town1 | v2 3 | town1 | v3 4 | town2 | v4 5 | town2 | v5 6 | town2 | ...
7
votes
0answers
66 views

How can I automatically split a spatial layer to a grid [duplicate]

Possible Duplicate: Divide vector in equal smaller parts in QGIS or similar I am a software engineer and the company I work for has a project that needs some spatial data manipulation in ...
6
votes
1answer
316 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 ...
5
votes
3answers
3k views

Bounding box for PostGIS table

Is there an easy way to get the bounding box for an entire table in PostGIS? Thanks
5
votes
1answer
327 views

How to filter points based on proximity while inserting into PostGIS?

I have a large amount of locations that need to be inserted to a PostGIS database. However, I'd like to aggregate these points so that if there's already a location, say nearer than a kilometer, then ...
4
votes
1answer
597 views

Select by attribute expression invalid in python

I'm not sure how my expression is failing. Any ideas? inPoints = "points.shp" i = 0 arcpy.MakeFeatureLayer_management(inPoints, "pts") arcpy.SelectLayerByAttribute_management("pts", "NEW_SELECTION", ...
4
votes
4answers
1k views

Updating 30 Shapefiles, adding attribute and populating based on data

I need to add an attribute to each shapefile attribute table in ArcMap, I was thinking to do it in SQL, but I'm not sure how to go about doing it since I'm very new to ArcMap. Can someone explain to ...
4
votes
1answer
2k 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
2answers
876 views

What is the best approach to using IQueryFilter.WhereClause in ArcObjects?

What is your preferred / best approach to using IQueryFilter.WhereClause statements in ArcObjects (e.g. when selecting features by attributes)? You need to check the source type (shapefile / personal ...
3
votes
1answer
69 views

New attribute creation using ogr and regex

I've got a folder full of dxf files with elevation values included as part of a long text attribute. I've written a regex expression to extract the elevation value, and would like to add it as a new ...
3
votes
2answers
1k views

How to write a 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

calculate value using maximum value from different feature class

I'm trying to get the maximum numeric value from one feature class and use that value in an expression to calculate values of a different feature class. fc2.bldg_id = max(fc1.bldg_id)+1 Can this ...
2
votes
1answer
321 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 ...