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

learn more… | top users | synonyms

2
votes
2answers
124 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
556 views

Sql Query for percent of buffer filled with objects

Hi I am trying to write a query to find the percentage of a buffer polygon (Called 250_Buffer) that is filled with or contains other polygons in another layer (Called Development) any help that you ...
2
votes
2answers
1k 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
872 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
2k 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
1k 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
669 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
694 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
543 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
362 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
310 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
195 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
181 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
565 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 ...
2
votes
3answers
701 views

Definition Query to Python script on Oracle 9i and ArcGIS 9.3.1

SUBTYPECD=1 AND SERIESNAME NOT LIKE '%-%' AND OPERATIONALSTATUS IS NULL OR SUBTYPECD=1 AND SERIESNAME NOT LIKE '%-%' AND OPERATIONALSTATUS = 976 The above is my def query in ArcMap against ArcSDE ...
2
votes
2answers
57 views

how to snap points to lines in QGIS/PostGIS?

I'm attempting to snap approximately 900 points to a line table. I've tried recreating what I have found from a couple of PostGIS answers but not having much luck. To test, I've created two ...
2
votes
2answers
319 views

Get Coded Value Domains from a Oracle geodatabase using PLSQL

i'm trying to get all coded value domains from a Oracle geodatabase. I dont have access to arcmap. I only have access to oracle 10g database. SELECT I.NAME Domain, ...
2
votes
1answer
147 views

How to populate geography point using ST_MakePoint(long, lat)

I have a table called customer_campaign, and there is an empty column geom(geography) in it. So I want to fill it up by using ST_MakePoint(long, lat) in PostGIS, lat and long are two columns in the ...
2
votes
1answer
303 views

Perform a group by query on geodatabase in ArcMap

I have a geodatabase called predictions.gdb and a feature class called output_gis_120_Project in that database. I imported this feature class as a layer in ArcMap. The attribute table with columns ...
2
votes
1answer
708 views

How to perform SQL query using .STContains in MS SQL Server

I have a table that stores polygons and a table that stores points with a value field. Both sets of data are stored using the Geometry type in MS SQL Server. I have a simple requirement - I need to ...
2
votes
1answer
82 views

Find nearest distinct features to a point

I am trying to select a single distinct features closest to an XY point in PostGIS. So I can find a single record for each distinct code by using: Select Distinct ON (code) * FROM table; Which ...
2
votes
1answer
229 views

Cannot load postgis layer from sql string in pyqgis

I'm trying to load a layer using the following code in qgis python console: sql = "(select the_geom, ID from mytable)" uri = QgsDataSourceURI() ...
2
votes
1answer
573 views

Query to select MIN and MAX values by groups

In ArcMap 10 i'm working in a Personal Geodatabase where the points (see screenshot below) represent the number of each house in the street. Each point has an ObjectID. This ObjectID is an unique ...
2
votes
1answer
168 views

Get list of all tables in CartoDB account with SQL statement?

How can I get a list of all available tables in my own authenticated CartoDB account with a SQL statement? This returns null: SELECT table_name as t FROM information_schema.tables WHERE table_schema ...
2
votes
1answer
769 views

is there any direct access to Mapbox data? (similar to CartoDB's sql api)

I've been asked to add a form to a map that will write data in the background (e.g., ajax to a magic file that does datawrites and some emailing, notifications etc) and a new marker will be added to ...
2
votes
2answers
243 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? ...
2
votes
1answer
230 views

How to split polygons with another polygon and inheritage data?

Let's say that we have two tables with polygons representing areas. Table A and Table B. Geometries from table A represents both the valid and invalid areas. And the geometries in table B represents ...
2
votes
2answers
1k views

Merge lines with the same 'name' attribute on QGIS?

I have a shapefile with a few thousand lines, each is a street segment. I'd like to merge all features with the same 'streetName' attribute into a single attribute. I only really care about the name ...
2
votes
1answer
948 views

How to get polygons from PostGIS table to PHP array best?

Currently I fetch polygons from a database table using ST_AsText(). The results are in text format, like the following. POLYGON((84 104,212 48,321 122,243 179,275 238,131 240,84 104)) I am not ...
2
votes
2answers
163 views

Trying to use an integer in a WHERE clause

I'm trying to use an integer value in a WHERE clause (looking at an integer field). expression = "'SERVICE' = 'Service' " expression = expression + 'AND ' expression = expression + "'GPS_TIME_TOTAL' ...
2
votes
1answer
284 views

SQL Command to convert pgRouting result to GeoJSON

My goal is to produce a Web UI for pgRouting using this tutorial. I have already imported my data from an OSM pBF to PostGIS using osm2po. Using the pgRoutingLayer plugin for QGIS, I was able to ...
2
votes
1answer
332 views

How to return nearest point to input lat/long with distance?

I am trying to write a query with PostGIS that returns one row where the geometry of that row is the closest to an input point. I would also like to include the distance between these points in the ...
2
votes
1answer
518 views

How to create a point table from a remote csv file?

The current approach that I am using to load a csv file into a PostGIS enabled database on a server is the following: -- create table CREATE TABLE points( id int, lat double precision, lng double ...
2
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
1answer
1k views

How to extract data from a POINT column in PostGIS using JAVA (jdbc)

How do you get a value from a column which has a point data type. I've just switched over from MySQL to PostGIS. I have a column which is called point and is of type Point. I want to get that value ...
2
votes
1answer
776 views

Convert SqlGeometry data from SQL directly to Tiles / PNG file

I would like to know a basic method for 'exporting' SqlGeometry data from SQL Server 2008 to a Tile Server / PNG File generator. I already have a Silverlight Map application which uses a Custom Tile ...
2
votes
1answer
38 views

How can I do a subquery inside a subquery in PostGIS?

I need to add a subquery to a working function. Instead of select * from table, I'd like to select * from (select * from table where height>1500) as sub, something that works in other functions I ...
2
votes
1answer
41 views

ST_Union based on attribute

I have a road network of center lines of MultiLinestrings.They have nodes at every intersection. I try to understand how I can merge roads together that have the same category as attribute (There are ...
2
votes
1answer
93 views

Trouble using ST_Difference to remove overlapping features

I am trying to use ST_Difference to create a set of polygons (processing.trimmedparcelsnew) that do not contain any of the area covered by another set of polygons (test.single_geometry_1) using ...
2
votes
1answer
50 views

SQL Server Geography Query works but Geometry Query Does Not - Different Tables, Similar Schemas

I am trying to get a Geometry query to work. A similar Geography query works fine but I must work with a table that uses a Geometry type. Although the Geography version returns lots of records as ...
2
votes
1answer
53 views

TSQL Geometry Query Requires conversion to string

If I specifically define some polygons and check for containment, I get a correct result: DECLARE @g geometry; DECLARE @g2 geometry; SET @g = geometry::STGeomFromText('POLYGON((407305 100826.5, ...
2
votes
2answers
189 views

Query for numeric field against text field that contains a numeric value

In my database, I have one field, Service_Day, formatted as numeric, which contains a single digit number 1 through 7 (for days of week). I want to compare it against another field, called Region, ...
2
votes
1answer
68 views

SQL Server equivelent to Arcmap Intersect for polygons?

I have three different sets of GEOMETRY Polygon datasets in SQL Server that completely overlap. I need to create a new dataset that has a polygon for each unique area of the three layers. So ...
2
votes
1answer
134 views

If do not want to use pgRouting then what?

Good evening fellow GISers, I am interested in creating an online routing application. So supposing I have all the data I want into a postGIS database and loaded onto GeoServer and I am willing to ...
2
votes
2answers
71 views

SQL 2012 code that spatially polyline fine (join file) to a point file (target) and provides Distance Field

I am currently working in ArcMap 10.2.2 but would like the SQL code to create the same output as that provided by the Spatial Join tool. Target feature = point, Join Feature = polyline, Option ...
2
votes
1answer
151 views

Model Builder Query by date

I've created a model that does several spatial joins based off of a lat/long in a table. In the table, I have a "Modified_date" which gives year, month, date, time. This model is set to run ...
2
votes
1answer
189 views

Trying to use SQL API from Oracle PL/SQL to CartoDB

I am trying to make an http call from within an Oracle server to CartoDB. To do so I have developed a custom PL/SQL package that will generate the SQL and make the calls. A sample API call is below. ...
2
votes
2answers
135 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. ...
2
votes
1answer
287 views

How to Buffer based on SQL expression?

Is there any way to optimize this code? It creates two buffer feature classes based on two different fields in the shapefile attribute table import arcpy from arcpy import env ## Set Workspace ...
2
votes
2answers
516 views

What is faster for geoprocessing (intersect) big shapefiles?

I have 200 Mb shapefiles of polylines downloaded from OSM. I've been working with QGIS 2.0 in Ubuntu 64 Bit 8 Gb RAM to make some geoprocessing tasks and it's taking 2 days for processing. Once I've ...