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

learn more… | top users | synonyms

4
votes
3answers
326 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
264 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 ...
4
votes
1answer
513 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
221 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
334 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
602 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
674 views

Execute SQL in File Geodatabase

Is there a way to execute SQL queries on file geodatabase from Python? I'm looking for the same functionality provided by arcpy.ArcSDESQLExecute for SDE databases. Thanks,
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 ...
4
votes
1answer
206 views

How to use %value% (from For iterator) in SQL expression of Calculate Field in ModelBuilder?

I have a shapefile with data of fish specimens and the coordinates where they were tagged for research purposes. One of the fields in the attribute table of these shapefiles is the fish length in ...
4
votes
3answers
163 views

Sql syntax in Model builder

In ModelBuilder I am trying to perform the following query, using the select by attribute tool. I am having great difficulty with the sql syntax. What I am trying to do is Select Top 3 (Avg) Price, ...
4
votes
1answer
313 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 ...
4
votes
1answer
64 views

SQL-query in arcpy - SELECT WHERE feature between two values

I need to select features between two values in python with arcpy. However, whenever I try to run the code below, it returns with error message "An invalid SQL statement was used". whereclause = ...
3
votes
3answers
2k 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 ...
3
votes
1answer
496 views

What does “foo” stand for in this PostGIS command?

The help page for the PostGIS function ST_PixelAsCentroids has this line of code as a usage example: SELECT x, y, val, ST_AsText(geom) FROM (SELECT (ST_PixelAsCentroids(rast, 1)).* FROM dummy_rast ...
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 ...
3
votes
3answers
948 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
84 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
2answers
102 views

How can I extract all distinct values from a PostGIS Raster?

I am working with an integer raster in PostGIS 2.1 and I would like to extract all distinct values in that raster. I was hoping to be able to do something along the lines of the following query, but I ...
3
votes
1answer
332 views

Why is Spatialite showing dates in MM-DD-YYYY format?

I have a table with a dates column that is formatted in MM-DD-YYYY format, as shown in SQL SELECT statements. I'm using Qspatilite (QGIS 1.8) and Spatialite GUI. When I ORDER BY the date column it ...
3
votes
4answers
9k views

Using String Functions (LEFT, RIGHT, MID) in Select By Attributes against a File Geodatabase using ArcMap 10

I finally (albeit reluctantly) made the switch from a personal geodatabase (pGDB) to a file geodatabse (fGDB) not too long ago. The main reason it took so long to switch has to do with how Select by ...
3
votes
1answer
660 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
967 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
4answers
4k 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
1answer
429 views

Edit SQL Server table (not registered with geodatabase) with arcpy

I am trying to update some rows in a SQL Server database table (not registered with geodatabase) with arcpy.da.UpdateCursor. However, when running the deleteRow(), I get the error return without ...
3
votes
3answers
1k 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
321 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
857 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
2answers
570 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
1answer
540 views

PostGIS intersection function

I have a problem with intersection function. I built my query following http://manual.linfiniti.com/en/postgis/geometry.html, 5.7 section. I made my clip object, a red linestring frame: But after ...
3
votes
1answer
673 views

raster2pgsql: Strange error when creating a table

Hi GIS Stackexchange people, When I try to load a asc file into my psql setup, I get a strange error message. I have created the sql input file via raster2pgsql -s 4236 -I -C -M gis_data.asc -F ...
3
votes
2answers
289 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
72 views

Buffer multiple roads to find points within and compare?

What I am trying to do is buffer a main st and a side street and get all of the points that intersect that buffer, however they need to be mutually exclusive. In the example below, my results would ...
3
votes
1answer
79 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
513 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
144 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
1k views

ESRI File Geodatabase - Trying to use CURRENT TIME in Definition Query

Is it me, or are the date functions in the file geodatabase a total nightmare. Anyway, I am trying to set up a fairly simple definition query that contains a subquery with a date comparison. My ...
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
2answers
2k 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 ...
3
votes
1answer
224 views

SQL query to have a complete geojson feature from PostGIS

I would like to get a geojson feature with properties from PostGIS. I have found an example to have a feature collection but I can't make it works for just a feature. so far I tryed to modify the ...
3
votes
2answers
726 views

GDAL SQL syntax to add field an put values

I try to make a new field in my attribute table and put in values from another field after converting from character to integer but can't get the right syntax. I run this as shell-script in MSYS. cd ...
3
votes
2answers
381 views

is there a public street database that I can do SQL queries on?

Sorry if this isn't strictly a GIS question. I want to know if there's a public database of US streets and US States that I can simply connect to (e.g. JDBC) and run SQL queries. For example, I want ...
3
votes
1answer
529 views

Parameterizing SQL Views Geoserver

I must do a view in geoserver. I seen some example here: http://docs.geoserver.org/stable/en/user/data/database/sqlview.html. The example show the operator like for parameterizing view. Suppose, now ...
3
votes
1answer
222 views

How to query against time

I have a date-time field in a feature class which I want to query. The field has values like "2011-01-01 00:00:29". I want to query the data between the time 00:06:00 and 00:12:00. The date does not ...
3
votes
1answer
516 views

Duplicate ObjectID in ArcSDE Table

After making a check in from an arcpad AXF file, i remark that there's duplicate object ID records , this Arcsde pgsql table is versioned and edited by Axf checkins and a .net engine application. the ...
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 ...
3
votes
1answer
71 views

PostGIS: Get closest point between geometry and Multipoint

I think my question is not that difficult for GIS professionals, but i didn't found an answer to my problem (even not in other questions). Problem: I have given two tables (table1,table2) with Points ...
3
votes
1answer
74 views

Showing Linestring from SQL statement with CartoDB

I have cartodb sql statement to return what polygons are intersected by a linestring, but I'd like to have the linestring visualized over the polygons on the results. Also not add the linestring to ...
3
votes
1answer
68 views

Use selected value in postgis function

How can I use the selected value from a SELECT statement in a PostGIS function. e.g. using the geom_way from SELECT geom_way FROM osm_2po_4pgr ORDER BY ST_Distance(ST_GeomFromText('POINT(21.323329 ...
3
votes
1answer
260 views

ArcMAP SQL Statement Iterate Feature Selection 'IN' 'BY' 'IS' - escape character

We have a problem when selecting Features from a Feature Class in ArcMAP 10.2 using the Iterate Feature Selection tool in Model builder Our dataset has a Attribute Field with two character codes like ...
3
votes
1answer
244 views

Triggers for delta tables

I have been facing an issue to capture the delta updates on SDE. We have written triggers on A and D tables to capture the delta updates(to capture the version name based on state id's), however I ...