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

learn more… | top users | synonyms

3
votes
1answer
110 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
128 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
78 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
272 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 ...
3
votes
1answer
352 views

What formula does SQL Server 2008 use in STDistance?

I am analyzing the accuracy of the STDistance function for geographical objects in SQL Server 2008, in order to understand its behaviour better I need to find the underlying formula. Does anyone ...
3
votes
1answer
229 views

Given a lat/long point, determine if it's land or water?

I have a PostGIS-enabled PostgreSQL 9.1 installation with the 10M physical dataset from Natural Earth imported. It's been converted to 900913, and in QGIS I can visualize the data. So the plumbing's ...
3
votes
2answers
167 views

Traversing a PostGIS table tree

I'm developing a geospatial db for a company that operates at a number of large facilities. Its primary purpose is to help staff locate themselves and equipment within each facilities' multi-storey ...
3
votes
1answer
732 views

Routing with multiple points

I am using postgreSQL, postGIS and pgrouting. Now I am really confused on how am I going to find the shortest path between multiple points. I know how to find it between two points through dijkstra ...
3
votes
1answer
185 views

Editing and Deleting Query Tables in ArcMap

How do I edit the query for a Query Table after it has been created? How do I permanently delete a Query Table? Even after I do right-click -> remove, it still appears in dropdown lists.
3
votes
1answer
282 views

Python SQL connection causes crash in ArcMap

I have the following code which I have striped down to just included the part that is causing a crash in ArcMap. import pymssql # Connect to the Research db using Windows Integrated Authentication. ...
3
votes
2answers
2k 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 ...
3
votes
0answers
88 views

Spatial query too slow to handle bigger OSM extracts

I'm trying to compute some statistics for OSM data using PostgreSQL 9.3.5 and PostGIS 2.1.4. I started with a small bavaria extract which I downloaded from Geofabrik. The db schema is the normal API ...
3
votes
0answers
90 views

Preserve datatypes when using SQLite and QGIS

Following Nathan's SpatiaLite/DBManager solution in this answer, I find that once the layer is loaded from DB Manager into QGIS (2.6.1), any calculated fields are appearing as text values instead of ...
3
votes
0answers
161 views

How to export data from sql server into shapefile in the decimal format in QGIS 2.4?

The last couple of days, I have been working on exporting data from SQL to a shapefile in QGIS 2.4. While converting into a shapefile, the data type changes automatically from Float to Integer in the ...
3
votes
0answers
92 views

How to express not within/inside predicate in spatial database with good performance

In our application we want to express some spatial predicates as objects not within some areas, but unfortunately it has very bad performance. The following SQL is generated by our application: ...
3
votes
0answers
283 views

Geoserver WMST Date Format

There is table in SLQ-Server 2008 R2. In this table i have date column with date format like this 2006-09-08 14:39:41.000. Now i make View from this table on Geoserver side but do nothing wiht date ...
2
votes
1answer
126 views

Case sensitive query of shapefile in ArcMap 10

I am an IT guy, not a GIS guy. What I know about how to use ArcMap 10 I learned from Google. It has served me well so far. Apparently what I'm wanting to do worked in version 9, but no longer does in ...
2
votes
2answers
75 views

Add a serial id column inside a view

Is it possible to create a new id serial column inside a view? I need it to be able to add my view in postgis I have made a full outer join on two tables I need a unique PK to load the view in ...
2
votes
2answers
258 views

Spatial join versus classical join

What are the main differences between a spatial join and a classical database join such as an equi (inner) join?
2
votes
3answers
108 views

Find point close to points from postgis table

I have a postgis point table with a large list of cities. I need to find duplicates in it, but only if they are 10km close each other, which means that is the same city. For example, I have 5 points ...
2
votes
2answers
444 views

Select by Attributes - If string has a letter

I'm running ArcMap 10.1. I'm using a File Geodatabase. I have a string coordinate field that I want to clean and some records have letters in it (mostly 'N' or 'E' or 'n' or 'e' e.g. '6213210N' or ...
2
votes
2answers
804 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 ...
2
votes
3answers
506 views

SQL expression error for Select Layer By Attribute

arcpy.MakeFeatureLayer_management("N05-12_Station2_JGD.shp","station_12") year=2012 arcpy.SelectLayerByAttribute_management("station_12","#",'"closing">=%d AND "opening"<=%d' % year) ...
2
votes
1answer
463 views

PostgreSQL, find points near points

I'm going to have a database with a 'people' table and a 'shops' table. I'd like to find all people within 500m of a shop, and details from both tables. I'm used to working with mySQL, but I'm ...
2
votes
1answer
105 views

how do I select more than one features from raster attribute table

for ex: "lc_2001" = 21, 22, 23, 24 AND "lc_2006" = 11, 31, 41, 51
2
votes
2answers
236 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 ...
2
votes
1answer
98 views

Spatial joining syntax

A standard PostGIS point-in-polygon query might look like this. (example tutorial) SELECT p.*,b.polyname FROM points p, shapes s WHERE ST_Within(p.the_geom,s.the_geom) Imagine this was not a ...
2
votes
2answers
163 views

Using MAX in SQL Expression of ArcGIS ModelBuilder?

I am trying to use a SQL expression in order to select values that are >= 25% of its max value in that column. For instance, VALUE >= MAX(VALUE)*0.25, and those will ultimately be the selected ...
2
votes
2answers
694 views

How to get the data type of each column from a postgis table

I need to get the column data types of all columns in a table, including the geometry types. What I wanted to know is if there is a function or SQL that gives something like this: column_name | ...
2
votes
2answers
195 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 ...
2
votes
1answer
2k 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
295 views

Trying to use Python code and user defined inline parameters to build a SQL expression

I am trying to build a tool where the end user can run a model and make a selection from a feature class based on 3 different fields, calculate the total shape length, & export the results to a ...
2
votes
1answer
85 views

How to select all tenth in a field? [duplicate]

I want to select all tenth in a field! :( I know this way; "Km" = 10 or "Km" = 20 ... and so, but I am pretty sure theres other way. I am not SQL literate, but with your help in this forums I have ...
2
votes
1answer
1k views

What is the proper way to delete an SQL Server database in order to delete an SDE geodatabase?

I'm new to this SDE stuff and would like to know the best way to cleanly delete an SQL Server database in order to delete the SDE geodatabase. Is it as easy as right clicking on the database in SQL ...
2
votes
1answer
2k views

How do I format SQL queries for use in Arcpy?

I have to make an SQL query on a column having not adequat name "type_borne_id", I couldn't make SQL queries using arcpy.Select_analysis neither selectbyattribute, but when I use select_analysis ...
2
votes
2answers
1k 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 = ...
2
votes
2answers
75 views

PostgreSQL/PostGIS Geom to JSON with Inner Join to another table

I am currently producing JSON from a database successfully using the following query: SELECT row_to_json(fc) FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features FROM ...
2
votes
2answers
61 views

PostGIS SQL NOT EXISTS Statements? [closed]

I'm trying to query my PostGIS database for ID of people who did X but did not do Y. I've attempted the query below, but it does not return any results. I'm very new to SQL so I may be overlooking ...
2
votes
2answers
708 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 ...
2
votes
1answer
583 views

Postgis: find geometry within multiple polygons

I've got two tables with geometry in it. Table 1:items holds items with a point geometry; while the second table property_zones holds polygons. The Items: CREATE TABLE public.items ( id integer ...
2
votes
2answers
1k views

Select layer by attribute using SQL statement LEN() in ArcMap

as the title already suggests... I want to query the length of a string column and thought to use: LEN([ColumnName]) > 5 However, that doesn't work at all. I'm querying a table in a fGDB in ArcMap ...
2
votes
2answers
975 views

How to convert Geometry type column to degree Minutes Second in SQL Server 2008 R2

I have shape column of type geometry and I want to convert X and Y coordinates into Degree Minutes Second format using SQL query and i am using sql server 2008 R2. Currently I got Lat and long using ...
2
votes
1answer
523 views

Trouble formatting SQL expression for where clause in “Search layer by attribute” arcpy function

I am trying to pass a variable into a where clause in the "Search Layer by Attribute" arcpy function. I cannot seem to figure out the correct format for the SQL expression so that the function reads ...
2
votes
1answer
528 views

How do I use the SearchCursor where_clause to get a max value from a column?

This is my first time asking a question on StackExchange. I hope I phrase my question and describe my attempts to understand my problem well enough! While working with SearchCursor, I'm trying to ...
2
votes
1answer
466 views

Select top 5 most items in a geodatabase

I'd like to know what is the best way to select the 5 most expensive items (in terms of actual monetary value) in my geodatabase where i have a price field. Prices are not necessarily ordered but can ...
2
votes
1answer
315 views

Why is my PostGIS transformation not working?

I am using a PostGIS query to find the intersection of two streets, and then transform the results into lat/lon. The street database's current SRID is 6856, a projection I added to PostGIS myself; and ...
2
votes
1answer
273 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 ...
2
votes
1answer
183 views

Postgis nested query trouble

I am having difficulty writing a nested postgis query. I have a single table from which I need to create a view in PostgreSQL. I have created the view with the following command create view ...
2
votes
1answer
175 views

SQL Plus error using sdeversion

I'm new to SQL Plus and I have a question. I've created several versions of a feature class located on a SDE database. I want to use ArcSDE Administration Commands found here but I'm getting an error ...
2
votes
2answers
522 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 ...