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

learn more… | top users | synonyms

1
vote
1answer
108 views

Read height data from Meridian 2

I'm having difficulties reading the Gridded height data (OS Meridian 2). I'm loading with OSGB 1936 reference system but have trouble finding the lat/lon data for each point. i.e.I open the attribute ...
5
votes
2answers
1k views

Changing Buffer table geometry dynamically in PostGIS using plain SQL

I have two tables in PostGIS, a point and a point_buffer table. The point table has a buffer_distance field, with a default value say 200. Now I want to change the buffer table geometry whenever I ...
1
vote
0answers
307 views

Manifold 'Link drawing to query' - can I do this in QGIS?

Manifold GIS allows the creation of dynamic drawings linked to queries. Unfortunately I do not have access to Manifold as my employer refuses to pay for it, so am currently investigating ...
1
vote
1answer
1k views

POSTGIS query of point feature. How to find nearest point to query location?

I am querying a point feature using a POSTGIS request fired in Javascript. The decoded URL looks like this and returns great results for polygon features. ...
1
vote
1answer
526 views

Creating SQL query in ArcGIS Explorer SDK?

I am using the ArcGIS Explorer SDK and have run into an issue surrounding a query I would like to perform with SQL. I believe my query is built wrong or perhaps I am looking at the wrong fields. Would ...
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)'), ...
9
votes
3answers
14k 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 ...
11
votes
3answers
4k 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 ...
0
votes
1answer
277 views

Vertex point is returning None

I have this multilinestring that has 48 NPoints. When I execute ST_PointN(line,5) it returns none. And to continue on, it returns None for everything after index:2 up to index:48. This is how my line ...
6
votes
1answer
2k views

Registering SQL Spatial View in ArcSDE and updating Row_ID?

I am having issues registering an inner join SQL view with a spatial column in ArcSDE. Any help would be much appreciated. Here is the background info: ArcSDE 10, SQL Server Enterprise 2008 R2, SQL ...
1
vote
1answer
615 views

Passing parameters to PostGIS SQL queries in Python: weird behaviour

The following code always goes to the except section and I do not understand why..Any help would be much appreciated. l = None c = conn.cursor() try: s = "SELECT ST_NPoints(ST_GeomFromText(%s))" ...
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 ...
1
vote
1answer
373 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
756 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 ...
5
votes
1answer
6k views

Arcpy cursors, WHERE clauses, and date/time fields

I'm having trouble making a selection of rows that meet certain date criteria using my arcpy.UpdateCursor(). I need to update records in a field in a geodatabase table with an ascending value based on ...
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 ...
7
votes
3answers
2k 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 ...
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 ...
11
votes
1answer
669 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 ...
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 ...
8
votes
3answers
6k views

How to find 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. ...
3
votes
2answers
3k views

Adding rows of data to a non-spatial table in ArcMap with a SDE SQL Server for Metadata purposes

I'm attempting to use several non-spatial tables to better organize the large set of feature classes I continually add to a SDE Geodatabase (SQL Server Express). So I have several tables setup and ...
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 ...
3
votes
2answers
2k views

Check if an object is empty in mapbasic

I have mapbasic code that manipulates the obj-column in a point-table. Some rows doesn't have any geometry so the obj-column is empty. My question is how to check if there is no object? All the ...
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 ...
7
votes
1answer
844 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 ...
4
votes
2answers
687 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)'), ...
4
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
638 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
418 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
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 ...
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?