Acronym for Structured Query Language. A syntax for retrieving and manipulating data from a relational database.
5
votes
1answer
140 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
1answer
1k 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.
...
5
votes
2answers
414 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
2answers
3k 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 ...
4
votes
4answers
207 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
2answers
159 views
Which Geodatabase supports SQL and is easily portable?
I want to choose the geodatabase type, which could be:
permits direct sql queries.
easily portable, because the app would installed in one computer.
I know that the personal geodatabase is a good ...
4
votes
3answers
902 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.
...
4
votes
2answers
432 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 ...
4
votes
1answer
160 views
SQL Server data modelling + spatial data = what book?
What recommended book can you provide about data modelling for geographical spatial data (geometry) in SQL server?
4
votes
2answers
5k 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 = ...
4
votes
2answers
145 views
PostGIS stored procedures
I am wondering if there are open source applications that make extensive uses of the special functions (the ST_* stored procedures) provided by PostGIS? I found many apps that support storing map data ...
4
votes
2answers
162 views
Calculate Field based on field value in a second table
I have a table in a MSSQL Server Enterprise SDE database. I need to join a table from a MS SQLExpress Workgroup SDE database and then calculate 11 fields in the Enterprise table based on values in ...
4
votes
1answer
691 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
1answer
151 views
ogrinfo SQL GROUP BY Clause
Does ogrinfo support a GROUP BY clauses? I could not find any good documentation of all the SQL syntax features it supports.
I tried the following:
ogrinfo . -geom=no -sql 'SELECT lsad FROM ...
4
votes
2answers
258 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
2answers
411 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 ...
4
votes
3answers
794 views
How to automatically create 2D-point features from addresses in an SQL database
I currently have an SQL-Table which contains our buildings. I already added the table and now want to create features ( 2D-Points ) from that information. The biggest problem I have is that the ...
4
votes
3answers
140 views
How to get a list of distinct values from a 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 ...
4
votes
2answers
113 views
how to cast integer to float in an ogrinfo sql statement?
I have a shapefile for which I would like to add the values in a column.
As the sum exceed the capacity of an integer, I need to cast values to a float.
I tried the following:
ogrinfo -sql "SELECT ...
4
votes
3answers
295 views
Incorporating SQL Statements in Model Builder
I have a point data set where each point has a unique operator associated with it. I'd like to select by attribute = operator name and generate a new shapefile for each operator containing only the ...
4
votes
1answer
485 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. ...
4
votes
1answer
313 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: ...
4
votes
3answers
228 views
How to join two tables using date, time, lat and lon as common field?
I am sorry if the question is elementary but I did not find any clear discussion or guide to understand how to run a query in postgresql. So that a pointer to another website would be also useful. ...
4
votes
3answers
129 views
How to select a number of rows based on current sort order
I would like to be able to select a certain number of rows from my attribute table based on their ordering and not their attributes. For example, if I sort by ObjectID, I then want to select the top ...
4
votes
2answers
399 views
Publish SQL Views from Postgres/Postgis to Geoserver
I've trying and reading how to publish a Postgres/Postgis View in Geoserver. I know that I can create an SQL View in Geoserver, but I'd like to define the View in Postgres, not in Geoserver.
Does ...
4
votes
3answers
265 views
arcgis10 filter dataset
Using ArcGIS 10 SP-4
I have a large dataset of sites that needs to be filtered down to a usable size. There is an attribute for the site_ID and another one for the site_type. There are duplicate ...
4
votes
1answer
187 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?
...
4
votes
1answer
193 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
1answer
414 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 ...
4
votes
2answers
2k 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 ...
4
votes
1answer
949 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 ...
4
votes
1answer
1k 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 ...
4
votes
1answer
141 views
Is it possible to run an arcpy execute SQL method without connecting to any database or server?
I want to simply process a layer by writing a tool script in a toolbox without having to connect any database, so I think I may have to set up a workspace environment in ArcMap, but I'm not sure if I ...
3
votes
2answers
1k views
Bounding box for PostGIS table
Is there an easy way to get the bounding box for an entire table in PostGIS?
Thanks
3
votes
1answer
72 views
PostGIS - improving sql statement
I've managed to create the below statement that creates a table based on a point in polygon query. However it creates a column that gives a true/false value which I then delete out all the f values ...
3
votes
3answers
628 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 ...
3
votes
1answer
535 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 ...
3
votes
2answers
460 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
386 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 ...
3
votes
3answers
329 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
...
3
votes
1answer
1k views
ArcGIS - Create a join based on multiple fields
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 ...
3
votes
1answer
183 views
SQLServer generating invalid WKT
I am a GIS novice using SQL 2008, with C# and the NetTopologySuite WKTReader type to work with some geometries.
I have some data which I have been able to save into the database but when I read back ...
3
votes
2answers
507 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 ...
3
votes
1answer
1k 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 ...
3
votes
4answers
3k 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 ...
3
votes
2answers
511 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 ...
3
votes
2answers
250 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 ...
3
votes
2answers
119 views
Get Spatial Table Information Oracle Spatial
How can I get the tablename, Srid and GeometryType of all spatial tables in Oracle(I use 11g)?
In postgis, I can do something like
select f_table_name,srid,type from public.geometry_columns order by ...
3
votes
1answer
91 views
Proximity query approach
Let's say that I have two tables in PostGIS, tracks and cities, each table has two columns, in both cases the columns are called name and way, name is a string, way is a geometry.
I want to retrieve ...
3
votes
1answer
179 views
How to update attribute values of points outside an area?
I have two datasets in PostGIS 1.5 (on Postgres 9.1) one of polygons and one of points.
I am hoping to create a SQL command that will select all of the points in a dataset that are not within of the ...
