Acronym for Structured Query Language. A syntax for retrieving and manipulating data from a relational database.
6
votes
2answers
267 views
PostGIS SQL query to find a polygon that intersects with a line
I need to build a PostGIS query that would select every polygon inside an area the river is flowing through.
I'd better describe with an example below: There is a river (blue line) flowing through ...
2
votes
1answer
86 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 ...
1
vote
1answer
132 views
What are FLOOR and CEILIING in PostGIS designated for?
Just a short question to lighten up my understanding of some PostGIS-code:
Regarding the creation of user-defined grids I often find the expressions CEILING and FLOOR. Like in this non-representative ...
1
vote
0answers
39 views
given sets of vehicle passing on each edge, how to get most used vehicle to avoid transition?
I have two tables: one, the resulting table from converting osm.xml via osm2po so I can use pgRouting, and another one that contains information on what edges each public vehicle passes through ...
5
votes
2answers
163 views
Does SQL Server need extensions to do spatial?
Does SQL Server need extensions to do spatial like Oracle does?
Just wondering if there will be extra costs for implementing a SQL Server backend to SDE.
Thanks
2
votes
0answers
67 views
Problem with SPIT (error while importing layers to POSTGIS)
I was attempting to import a polyline roads layer into postgis, using both SPIT in QGIS 1.7 and postGIS GUI for this purpose. I have received errors through both processes as described here:
SPIT
...
4
votes
1answer
162 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
3answers
132 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 ...
3
votes
2answers
121 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 ...
5
votes
1answer
145 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
165 views
How to run custom queries on vector layers and display results?
Is there a way to display the result of a query in QGIS?
For instance :
To get the total number of inhabitants :
SELECT SUM( inhabitants ) FROM countries
To get the total density :
SELECT ...
2
votes
1answer
200 views
STIntersects - Update field to give me count of points in polygon
I have a points layer (dbo.ptLayer)
Around 1M points
Spatial Geometry Type (dbo.ptLayer.geom)
No spatial index just yet, but will create one once data gathering complete.
I have a polygon layer ...
4
votes
2answers
120 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 ...
0
votes
0answers
60 views
Can osm way records in SQL spatial be merged to one geographic record if they have same Id?
I've imported England shp file of roads to mssql using OSM2MSSQL and I've noticed that the same road has couple of records with same Id but different geographic (probably it was segmented).
Is there ...
1
vote
0answers
183 views
How to handle postgresql/postgis geometry type errors in a function?
I have created the following function in PL/SQL to make a polygon from a set of linestrings:
CREATE OR REPLACE FUNCTION _myfunction(
_fid integer,
_rid integer
)
RETURNS VOID AS $$ ...
2
votes
1answer
140 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 ...
0
votes
0answers
39 views
Possible to manipulate data in a postgres database with psql using JDBC or Python's psycopg2 with something else?
I ultimately want to manipulate data in a postgres database programmatically, and I am much more comfortable programming in Python than Java, but I'm not sure whether arcpy can ultimately use psql, ...
6
votes
3answers
129 views
Are there any open source Python modules compatible with arcpy that will allow complex SQL queries?
I am trying to handle string (non-geometry) duplicates in a large attribute table of a shapefile without having to convert to gdb and back or connect to a postgres db. I need to be able to use a count ...
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 ...
2
votes
1answer
260 views
How do I select * rows of multiple columns in one query using Select by Attributes within ArcMap?
I am trying to convert a psql query string into arcpy formatting for an UpdateCursor where expression parameter:
'''SELECT ...
0
votes
0answers
67 views
How to write model in ModelBuilder which connects to SQL Server database and merges subparcels by unique parcel Id?
How to draw a model in model builder; conecting to the database through SQL Server and merging the subparcels of a parcel who has unique Id?
Any help is much appreciated.
4
votes
1answer
142 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 ...
5
votes
1answer
128 views
Join based on maximum overlap in PostGIS/PostGresQL?
I have two sets of polygons in two tables. The sets overlap each other. For each polygon in set A, I would like to get the ID of the polygon in set B that it overlaps the most. I'm using PostgreSQL ...
1
vote
1answer
132 views
Should I query the tab/shp file or SQL (mapserver or geoserver)?
I have tab files (or shp) with information like residents , buildings etc...
I need to be able to click a point in web map and query all data that fall in certain radius.
I didn't decide yet which WMS ...
7
votes
2answers
550 views
Select by Attributes based on SQL wildcard range
I want to select all the records from a geodatabase table where the string starts with a letter, so I tried
SELECT *
FROM tbl_names
WHERE "name" LIKE '[A-Z]%'
This returned no records. After some ...
3
votes
1answer
72 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.
4
votes
3answers
297 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 ...
3
votes
1answer
277 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 ...
7
votes
0answers
64 views
How can I automatically split a spatial layer to a grid [duplicate]
Possible Duplicate:
Divide vector in equal smaller parts in QGIS or similar
I am a software engineer and the company I work for has a project that needs some spatial data manipulation in ...
6
votes
0answers
210 views
Getting ST_Difference between two different tables in postgis [closed]
One table has a series of grid squares in it, and the other has millions of voronoi polygons in it. I need to create a table that has the difference between the two of the them so that I effectively ...
1
vote
1answer
121 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 ...
3
votes
1answer
116 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
1answer
70 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
3
votes
1answer
87 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 ...
0
votes
0answers
620 views
How to populate data from postgresql using dynamic php\html listing
In the form I created, I need two fields to be dynamically populated from the database while the other fields will be manually entered and push to the database. I have written a script that is able ...
2
votes
1answer
113 views
How to create a network with all the track points
I have created a sample network through the use of gps visualizer. Than I used the ogr2ogr function to import this data in my database.
Now I have a table which holds all the track points of these ...
3
votes
1answer
399 views
Python script for constructing a WHERE clause from user input
I'm trying to construct a where clause from user input that will eventually be passed through as a SQL statement in the select tool or the select by attributes tool. I've started a script thats been ...
2
votes
4answers
2k 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 ...
0
votes
0answers
63 views
Coordinates move in sql server insert
When I insert a row in TableA in sql server I use this for the geometry column:
geometry::STPolyFromText('POLYGON((x1,y1,x2,y2,x3,y3,x4,y4,x1,y1))', 3007)
You can find the definition for ...
5
votes
1answer
174 views
Clarification of Logical Operators in a PostGIS Database
The following query returns this result:
select count(*) from a , b where a.geom && b.geom;
count
-------
66514
On a postgis 2 database.
What does the logical operator AND ...
1
vote
1answer
135 views
run sql on Qgis
I want to do simple demo to our manager with quantum gis. i have buildings from my city with wgs84 projection on postgre sql database and also have non gis table which have who leaves this buildings ...
2
votes
1answer
355 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 ...
0
votes
0answers
34 views
How to select from an ODC Table?
I want to run the following query, using Esri's Select by Attributes tool, on a table found in an ODC. After selecting these rows, I want to append the records to a table found on an SDE connection. ...
1
vote
0answers
56 views
Database has duplicate values after running pgrouting
I am a newbie with postgresql and sql statements so maybe it is a trivial question, I'm sorry for that.
I am working on a postgis database and try to use the pgrouting function. My goal is to store ...
3
votes
1answer
92 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 ...
1
vote
2answers
168 views
How to find the center point of MULTILINESTRING?
How to find the center point of MULTILINESTRING, which lies on the multiline?
1
vote
2answers
391 views
How to offset point perpendicular to line direction in PostGIS
I have a postgis table of points, that are sitting on road centerlines (I've linear referenced them). And the second table with road centerlines themselves.
The point table also has a field roadid and ...
4
votes
2answers
419 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 ...
3
votes
3answers
339 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
...
2
votes
1answer
73 views
Join Zip Codes Table to Nearest City from Second Table
I have two tables, siteareas and ziplookup. The former contains large cities in the US; the latter all US zips with lat/lngs. I have a foreign key into the former table in the latter: sitearea_id. I ...
