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

learn more… | top users | synonyms

0
votes
1answer
224 views

Running database operations against a stored GIS geometry

I'm using Quantum GIS and Postgres with the GIS extensions to store shapefiles that I'd like to run point in geometry and point in union geometries against. While it's easy to save and load the vector ...
0
votes
0answers
62 views

postgis minimum distance among many points [duplicate]

Possible Duplicate: Nearest Neighbor calculation in PostGIS Is there a way to achieve the following: SELECT ST_AsText(a.geometry), min(ST_Distance(ST_GeomFromText('POINT(10 20)'), ...
4
votes
2answers
1k views

Selecting points not snapped

I have a point layer and a polyline layer. I want to select all points in the point layer that are not snapped to a vertex in the polyline layer. How can I accomplish this? I'm using MapInfo and ...
4
votes
2answers
1k 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 ...
1
vote
1answer
375 views

How to correctly use cursor.execute() with PostGIS?

I do not understand how the operator works some times... Here is an example that works, "SELECT ST_Distance(ST_GeomFromText('POINT(-3.16496271127842 ...
6
votes
2answers
1k views

How to fix pgRouting error “starting vertex not found”?

I've posted on here before about my battles with drivetime polygons and got some sensible suggestions back, so having Googled myself stupid over this I'm turning to you once again in the hope of a ...
4
votes
1answer
2k views

Good place to get Country, State, City, and Zipcode information?

I'd like to populate a database with common location information. I'm not entirely certain that addresses across the globe have similar or even the same entities as the Country, State, City, and ...
3
votes
1answer
758 views

ArcGIS Select Analysis using Unique Values from Field

I am trying to create separate shapefile using Select analysis with ArcPy by doing an SQL query to create them based on a unique value from the field. I want to create a list of the unique values in ...
0
votes
1answer
100 views

How to query distict row with the same label from different linestring with multiple segment?

I have a multilinestring street table in postgis with two columns: label and prefix. This table has 2 different roads with the same name and each road contains multiple segments. Roads were splitted ...
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 ...
0
votes
2answers
1k views

Correct SQL statement in FME?

I am trying to write SQL statement in WHERE Clause FME in Navigator but it does not work. WHERE Clause: NAME IN SELECT CONCAT(''$(',SELECT REPLACE ('Roman Ann Jack',' ',','),')'') This sql code ...
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 ...
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 ...
7
votes
1answer
845 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
1answer
1k views

PostGIS and invalid SRIDs

I'm having difficulty with a PostGIS query. I am trying to query my database (world_countries) using a bounding box drawn on a google web map. The objective of this query is to find any countries that ...
8
votes
1answer
122 views

How to rectify the walls of buildings in PostGIS?

I need an operations in PostGIS to eliminate intermediate vertexes in a polygon to rectify the walls of a building. I was revising http://trac.osgeo.org/postgis/wiki/UsersWikiExamplesSpikeRemover but ...
4
votes
2answers
688 views

Merge reversed linestring in correct order in postGIS

I want to merge linestring in the correct order in postGIS, but sometime they are reversed so something like this : select astext(linemerge(ST_union( ST_GeomFromText('LINESTRING(3 3, 2 2)'), ...
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, ...
2
votes
2answers
644 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 ...
7
votes
1answer
425 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 ...
5
votes
1answer
419 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 ...
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 ...
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 ...
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
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
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? ...
8
votes
1answer
493 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 ...
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
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 ...
2
votes
1answer
881 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 ...
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 ...
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 ...
5
votes
2answers
11k views

'Select by attribute' using python in ArcMap 10

I need to select a feature using SQL before zooming to it in python. What I'm looking for are replacements for the commented lines in this code: import arcpy parcel_num = "123456" mxd = ...
2
votes
3answers
714 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 ...
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 ...
4
votes
1answer
718 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
309 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 ...
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. ...
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
822 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. ...
6
votes
1answer
324 views

Postgres/postgis cursors

I have a road network with StreetName and min/max address ranges for each segment (ie- StreetName = 'Main St', Min = 100, Max = 199). I also have individual address points with similar information ...
3
votes
2answers
588 views

Is there a way to update the attribute table of a feature class with a query?

I have a layer with multi-polygon feature classes for states and provinces. The shape area for some features classes is < 0 causing the label to be position in odd locations. It seems that the ...
8
votes
2answers
756 views

Splitting polylines using regions with PostGIS

I was wondering if it is possible to split a polyline on polygon boundaries (regions) in PostGIS, the picture below is of what we have at the moment and showing where I need the polylines split: ...
4
votes
1answer
608 views

Geodjango insert statement: psycopg2.IntegrityError: violates check constraint

Why is the following INSERT statement throwing this error? INSERT INTO domes_place (placeid, structidx, oscode) VALUES ('10', '1', 'POINT(51.121282945772 1.3138398501222)'); psycopg2.IntegrityError: ...
3
votes
2answers
2k views

Error adding data to Geodjango database: parse error… within geometry

I'm trying to insert some values into a Geodjango database, but getting errors. This is the table: class Place(models.Model): placeid = models.IntegerField(primary_key=True) structidx = ...
8
votes
4answers
2k 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 ...