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

learn more… | top users | synonyms

5
votes
3answers
3k views

Inserting polygon centroids into an additional column in PostGIS

I'm trying to calculate the x and y centroids for a table using ST_Centroid. The documentation says that it returns the geometric center of a geometry. I first made a column X: ALTER TABLE ...
5
votes
2answers
2k views

How to export a PostGIS raster to PNG?

I have a raster which is split into 30 tiles and I want to dump them all into PNGs. I've read the documentation http://postgis.net/docs/RT_ST_AsPNG.html but still don't know how to dump it well. If ...
3
votes
2answers
2k views

Deleting records from multiple, similar feature classes by attribute values using SQL statement in ArcGIS Desktop?

I have several hundred geodatabase feature classes which all have the exact same fields. I need to go through each of those feature classes and delete any records which match an SQL query (the same ...
8
votes
3answers
6k views

Bounding box for PostGIS table

Is there an easy way to get the bounding box for an entire table in PostGIS? Thanks
13
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 ...
0
votes
2answers
1k views

Mysterious MapInfo query syntax errors

I'm struggling to get what seems like a perfectly normal query to execute in MapInfo v10. In SQLite Manager (an excellent firefox plugin), this works as expected: SELECT area_name, street_name, ...
3
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 ...
2
votes
2answers
655 views

Parameterized insert query to PostGIS fails

I have a PostGIS database table that I need to insert data into. I'm using Python 3 and the pg8000 interface to Postgres. Somehow, parameterized inserts do not work. If I do iq="INSERT INTO ...
5
votes
1answer
422 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 ...
1
vote
0answers
315 views

T-SQL with SDE and Web Map ArcGIS Server 10 MSSQL 2008

I need to build a system whereby the user of a web map (the standard web map that ArcGIS Server Manager builds) can edit a feature class's attributes. The attribute will be a 'Comments' field. The ...
2
votes
2answers
245 views

Is there a way to save manual QGIS changes to Postgis to SQL script?

I am manually editing a Postgis layar with QGIS and would like to export these manual changes in the form of an SQL script. This way: I can do it automatically next time i need to it is easier to ...
3
votes
2answers
2k 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 ...
7
votes
4answers
3k views

SQL expression for finding strings including letters in ArcGIS 9.3.1

I'm working on ArcGIS 9.3.1. I have a shapefile with a string field containing point names like these: 10 12 12/13 t/9 6577y 1234 12t9 124y67 u9 and so on (and yes, I know it looks like a colossal ...
5
votes
5answers
4k views

ExecutING SQL script in ArcGIS field calculator?

I am trying to transfer over row data from a table to columns in a shapefile table. There is a common "SA" variable between the two (i.e. the study area number) but there is more than one land use ...
7
votes
1answer
433 views

Get all Nodes from a List of adjacent LineStrings in PostGIS

I use PostGis and pgRouting. I have a table with geometries of the adjacent edges returned by a shortest path query. I want to concatenate these edges and retrieve a list of all nodes these edges ...
7
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 ...
4
votes
1answer
597 views

Shorter Version of this SpatiaLite Query

I've been doing some spatial queries using spatialite, and they're large enough that a spatial index becomes a must. An example of using an index is given in the tutorial spatialite> SELECT ...
4
votes
1answer
253 views

PostGIS Query: All rows within 1 hour of each other which intersect

I'm using postgres (postgis) and i want to perform a query that returns all rows which geometries intersect and have a timestamp within 1 hour from each other. What would the SQL statement look like? ...
9
votes
2answers
5k views

PostGIS: 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 ...
1
vote
1answer
1k views

MapInfo equivalent of SQL 'AS' statement

I'm trying to execute a SQL query in MapInfo, and want to change some of the column names to something more readable. The SQL command 'AS' doesn't seem to work - is there a MapInfo equivalent?
2
votes
1answer
2k views

How to extract data from a POINT column in PostGIS using JAVA (jdbc)

How do you get a value from a column which has a point data type. I've just switched over from MySQL to PostGIS. I have a column which is called point and is of type Point. I want to get that value ...
3
votes
2answers
3k views

Reading Coded Domains from ArcSDE 10 RDBMS using SQL/ADO.net

I need to read ArcGIS Domain Coded Value pairs (value/description) from SQL Server / Oracle in SQL (i.e. not using ArcObjects). This is an XML field. I can get the domain definition thus select ...
7
votes
1answer
1k views

Editing tables using SQL in ArcMap [duplicate]

We've recently started working with ArcMap, having used MapInfo previously. I've created a shapefile in ArcCatalog and want to update some columns using SQL. In MapInfo I'd write the following command ...
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 ...
4
votes
2answers
1k views

Using a bounding polygon, instead of a bounding box

I've got a large set of LAT/LON coordinates. Up until now, when I needed to select out those that fell within a certain region, I'd just create a bounding box using some MINx, MAXx, MINy, MAXy values ...
1
vote
0answers
229 views

Color polygons using SQL Database based Rules in Openlayers

I want the rules to be applied according to values in a database. Is it possible to include external database information that is not in the shapefile? In spoken words the original rule is "Color all ...
2
votes
1answer
893 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 ...
5
votes
2answers
408 views

Selecting POIs around specific buildings using PostGIS

The problem: I need to select, for each residential building in my table that has say at least 2 pharmacies and 2 education centers within a radius of 1km, all POIs (pharmacies, comercial centres, ...
4
votes
1answer
398 views

Sort by geometry using INHERITS?

hope everyone's doing well. I have a statewide point data set (parent table: 600K rows, 500 columns) I need to analyze at multiple scales based on several different spatial divisions. I'll also need ...
3
votes
3answers
12k views

Defining a Query with Multiple Values

I have been trying by best to create a definition query which would select a group of municipalities from among the many in my state. I have tried commands of all sorts of variations from ...
2
votes
3answers
715 views

Definition Query to Python script on Oracle 9i and ArcGIS 9.3.1

SUBTYPECD=1 AND SERIESNAME NOT LIKE '%-%' AND OPERATIONALSTATUS IS NULL OR SUBTYPECD=1 AND SERIESNAME NOT LIKE '%-%' AND OPERATIONALSTATUS = 976 The above is my def query in ArcMap against ArcSDE ...
8
votes
1answer
497 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 ...
6
votes
1answer
1k 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 ...
11
votes
3answers
2k 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 ...
8
votes
3answers
3k 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
1answer
722 views

Error using ST_AddPoint for adding a new point to a polyline in PostGIS

I have two PostGIS tables, one where I store points, named "nodes" and another with where I store multilinestrings, named "lines". I'm trying to append a new vertex to a line using one of the nodes ...
3
votes
2answers
310 views

SQL statements on a Geodatabase

I have converted a shapefile into a geodatabase which I use for geocoding via the ILocator interface. It has the extension (.mdb) and contains address fields with a point which is stored as a long ...
12
votes
2answers
11k 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 ...
5
votes
2answers
261 views

ArcSDE recently modified geometries

ArcSDE 10 with a MS SQL 2005 backend. I am trying to see the most recently modified geometries using SQL so that I can use it to update another GIS database running PostGIS. Has anyone tried this ...
8
votes
4answers
4k 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 ...
9
votes
3answers
4k 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, ...
9
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. ...
18
votes
5answers
7k 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 ...
0
votes
0answers
187 views

VB Script for selecting multiple records [duplicate]

Possible Duplicate: “If then” VB Script code (or Python) equivalent to SQL's IN ('x','y',z') expression in ArcGIS Field Calculator question? going to ask ...
5
votes
1answer
825 views

Spatialite SpatialSQL Query

I've been flummoxed by this query. I have a table of points, and a table of lines, and I am trying to return the point id and line id whenever the point is contained within a buffer of the line (i.e. ...
9
votes
1answer
4k 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 ...
6
votes
2answers
1k views

Using integer variable in where clause of ArcPy script?

I'm trying to include an integer variable in the SQL where-clause of a tool like this (ArcGIS 10 Python script): newR = ExtractByAttributes(inR, '"IntField">=2508') where inR is an integer ...
4
votes
3answers
1k views

How to label features in QGIS using SQL statement?

In QGIS, I can't seem to find a way to define the features that I want to label using a SQL query? Is this possible? For example, I downloaded the OSM Natural shapefiles, and a lot of those features ...
1
vote
1answer
965 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 ...
4
votes
4answers
7k views

Select by attributes - records with the largest attribute value

Using 'Select By Attributes' how can I select records from a shapefile database table where a numeric field contains the largest value. Need to use MAX function or something like that but can't get ...