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

learn more… | top users | synonyms

1
vote
1answer
35 views

How to refer to a variable from the conditional statement in the ExtractByAttributes function in ArcGIS 10?

Trying to use the ExtractByAttributes for rasters so that I have multiple attribute values that I want to extract. This works fine: myraster = arcpy.sa.ExtractByAttributes("source_raster", "VALUE IN ...
4
votes
2answers
979 views

Import large CSV file into PostGIS

I am trying to import CSV files into PostGIS. Following this post, I have created tables before. I found other suggestions saying that I can run the copy command. If I run this command: COPY table ...
2
votes
1answer
477 views

Cartodb - SQL query of public visualization of a private table

The team at CartoDB have recently(last few days) made some useful updates that now allow public visualisation maps to reference data in private tables. The change has only been made in the last day ...
0
votes
1answer
43 views

How can I combine two datasets with the same schema in to one table?

So I have two tables (in CartoDB/PostGIS) that are from two tiger shapefiles that have the exact same attributes, just in different counties. I want to combine them in to a single table, just ...
0
votes
1answer
158 views

How can I show numbers as percentages in CartoDB?

I would like to format numbers as percentages in cartodb. What are the best functions to display numbers with decimals, comma separators or percenteges? Thanks
0
votes
0answers
82 views

Group Nearest neighbor query in SQL Server 2012 spatial database

I am looking for way to perform GNN query over road networks in SQL Server. Any pointers
0
votes
1answer
28 views

Flag More Than One Space in SQL

I am working on an XP machine with FME'13 and I have a Address field and I have to find the spaces(middle spaces and not the leading and trailing ) in each and every row. St._Petters_Road is the ...
-1
votes
4answers
867 views

What is a SQL Query to all values in a column with Select by Attribute in ArcGIS? [closed]

I would like to select all values for an attribute using select layer by attribute to use in model builder and hopefully save the expression for automation purposes. If I have an attribute named ...
0
votes
1answer
199 views

How do I query XY Event Layer in Model Builder?

I am trying to build a model to input table data, make xy event layer and then select features by an SQL expression and make that a shapefile. What tool should I use for the SQL expression to select ...
1
vote
3answers
197 views

How do I use SQL in the field calculator to populate a new column?

Courtesy of Geofabrik I have a "places" point shapefile which includes in the attribute table a column named "type". As can be seen in the attached image the column contains fields such as "city", ...
0
votes
0answers
20 views

GeoDatabase user access based on administration boundry

I have silverlight application consuming Map service. Now I want to create user login. The scenarion is somethinkg like when a user logs he should only be able to query on data belongs to his area. ...
1
vote
2answers
116 views

How can I create a hexbin layer using the SQL and CartoCSS parameters in cartodb.js?

CartoDB has a great hexbin visualization wizard baked into the client dashboard: However, I like using cartodb.js to customize events and interactions; in particular, I'd like to make styles ...
0
votes
2answers
54 views

sql query to get origins and destinations

I have a table of cities with an ID and geometry (200K rows) and a table of travels, with only two ID: idfrom, idto (7K rows) I'm wondering how could I select the geometries for each one in order to ...
-1
votes
1answer
170 views

Shortest Path Dijkstra

I would like to calculate the shortest path with dijkstra algorithm. sample query SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra(' SELECT gid AS id, ...
0
votes
1answer
94 views

How to cut/split multipolygon with blade (linestring) into two pieces using PostGIS? [closed]

I struggle to do split operation in postgis I tried to build a query but Im always fail. Id like to cut a multipoligon with a blade (a linestring) into two pieces. These two objects have specific ...
1
vote
1answer
58 views

Rewrite query to match two points with a linestring in a different database table

OK, not sure if I've worded the title brilliantly, but here goes: Suppose I have: a table "gps_nodes" containing columns: "EASTING" (containing longitude data) "NORTHING" (containing latitude ...
3
votes
1answer
474 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 ...
1
vote
2answers
128 views

How to form SQL Expressions with mix of integer/numeric and text/string fields/values in ArcPy?

I am trying to create a query for the select by attribute function within a Python script.This is my current code. lot = '96' dp = 756421 lotFile = ...
5
votes
1answer
180 views

Is it possible that a good spatial index just cannot be made?

I have about 6 million rows with geography spatial features - they are all 1000 meter radius hexagons all over the US. There are a couple hundred thousand that would have been NULL, which I updated ...
1
vote
2answers
99 views

SQL: Select aggregated Bounding Box of multiple Features

I would like to calculate the aggregated bounding box of multiple features using OGC Simple Feature SQL or SQL API of ArcSDE. The following statement will only query the bounding box of each feature: ...
1
vote
1answer
142 views

Calculating difference between bearing values in degrees

Suppose I have 3 geographic points, A, B and C. I can calculate the bearing of B from A (in degrees) using this SQL snippet: degrees(ST_Azimuth(geogA,geogB)) as brg_a_b ...and the same of C from ...
4
votes
2answers
571 views

SQL Syntax to extract (convert) Strings with Degrees Minutes to Decimal Degrees

I have a PostgreSQL/PostGIS database with a two string fields containing Lat and Long coordinates in degrees and decimal minutes, like this 45° 24.5' N 3° 43.01' E. How can I convert them into ...
1
vote
3answers
257 views

Differences between SQL dialects in QGIS vs ArcView vs Oracle

I'm kinda new with SQL queries and there is something I don't get: A SQL query in QGIS is written differently than the same query in ArcView and will probably also be written differently if I want to ...
3
votes
1answer
63 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 ...
1
vote
1answer
36 views

How to do “random” distribution of certain classes to certain amount of polygons with respect to spatial constraints

I have a polygon layer. Now, I would like to assign/allocate/classify these Polygon into 13 different classes respecting spatial constraint, in my case: neighboring polygons (sharing line or nodes) ...
2
votes
2answers
134 views

Can I use the LEFT (string) operator in ExecuteSQL()?

In QGIS I can use the LEFT operator on a dataset like this: LEFT( "Name2",3) ='031' What is the syntax for using this expression with ogr in python? Isn't this the right syntax? ...
0
votes
0answers
63 views

Multiple geometry in a cursor SQL

I have a cursor which I create from a table which has one geometry field only. But in my cursor I would be using two geometry fields as I need to calculate two different areas.Below is the sql ...
1
vote
1answer
393 views

How do I insert a geometry as WKT to SQL Server Spatial with PHP?

I have a web mapping application built on Bootleaf (Leaflet + Bootstrap). Using the leaflet Draw plugin the user can create a polygon on the map. I'd like to insert that polygon into a geometry ...
3
votes
1answer
641 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 ...
2
votes
0answers
34 views

Multiple Cursors

Below is the sql statement which I have explained below. Declare Cursor c1 is select store_number ,0 as total_area,0 as BG_ID ,0 as store_geom from table1 where CLIENT_ID= 1 and ORG_ID ...
0
votes
0answers
30 views

SQL cursor issue

I am trying to create a new table using a cursor and a select statements. Declare Cursor c1 is select store_number from table1 where CLIENT_ID= 1 and ORG_ID = 1 ; Type C1_TAB_TYPE ...
1
vote
1answer
471 views

Trouble with INSERT in PostgreSQL 9.1

I am trying to execute the following: INSERT INTO lithic (cp_east, cp_north, type, material, art_count, comment, field_no, gps_unit, initials, rec_date, cortex, geom) SELECT (cp_east, cp_north, ...
2
votes
2answers
92 views

shp2pgsql mangles date field — quotes data as if it were char or varchar

I'm attempting to import a shapefile into postgis via shp2pgsql. Among the fields in the shapefile's .dbf file is a date field with format yyyymmdd, which is a valid date format for postgresql. ...
0
votes
0answers
63 views

Is there a limitation in quantity of joined tables using spatialite views?

I've one point layer in qgis and several non spatial tables in a spatialite database. Now I want to create a view as you can see in the following: CREATE VIEW testansicht10 AS SELECT ...
1
vote
1answer
167 views

arcpy: can I use SelectLayerByAttribute to compare fields from joined tables?

I can't figure out the syntax to select table records where one field's value exceeds that of another. The fields are from a pair of table views connected w/AddJoin(). One view is of a fgdb table and ...
0
votes
1answer
51 views

Issues with pgrouting workshop code in postgis 2.11/pgrouting 2.0

I'm having issues getting the pgrouting workshop to work on Postgresql 9.2/PostGIS 2.11 and pgrouting 2.0 on windows. How can I rewrite the following php/sql code and make it compatible with my ...
4
votes
3answers
461 views

How to create Where Clause from user input via Python list?

I am trying to perform a query based on values in a Python list. The where clause will then be used in an Update Cursor, so that the rows NOT equal to the values in the list will be deleted. This is ...
0
votes
1answer
69 views

Create Area or Polygon based upon a Series of points

i have a large list of points that i would like to merge to create an Polygon based upon an attribute. Essentially an area that encompasses all points. x | y | attr ____________________ 1 | ...
0
votes
1answer
571 views

Where Clause problems when all parts are user input variables

I have written some generic user ipnut code for a toolbox script (ArcGIS 10.0) but because everything is a variable I don't know how to write the SQL whereclause. Please can someone help and suggest ...
2
votes
1answer
251 views

Direct DBMS sql queries and ArcSDE geodatabase

i want to make updates from Arcobjects on several tables stored on a ArcSDE 10.1 versionned geodatabase based on Postgresql 9.0.5 , none of these update require spatial data , and making these updates ...
3
votes
2answers
1k 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 ...
1
vote
4answers
645 views

How to find the center point of MULTILINESTRING?

How to find the center point of MULTILINESTRING, which lies on the multiline?
4
votes
1answer
342 views

How to populate SQL options for parameter in ArcGIS 10 tool?

I'm trying to create a tool where individuals can select a variety of records using a SQL expression that is then passed to a calculation. I can add a parameter under the tool, and select SQL ...
3
votes
1answer
364 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 ...
14
votes
6answers
4k 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 ...
4
votes
1answer
704 views

Select by attribute expression invalid in python

I'm not sure how my expression is failing. Any ideas? inPoints = "points.shp" i = 0 arcpy.MakeFeatureLayer_management(inPoints, "pts") arcpy.SelectLayerByAttribute_management("pts", "NEW_SELECTION", ...
0
votes
0answers
74 views

Select By Attribute sql problems [duplicate]

Having trouble with this python code. I keep getting a ERROR 000358: Invalid expression" code for the select layer by attribute management statement towards the end of the code. Moved the data to ...
2
votes
2answers
421 views

CartoDB- Passing selected features bounds to map center parameter (Zoom to selected features)

Looking to build a light-weight viewer for some data we have hosted on CartoDB. I'm looking to have a user input their address into an input, pass a query to CartoDB and have the map zoom/pan to the ...
6
votes
3answers
1k 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 ...
3
votes
2answers
906 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 ...