Tag Info

New answers tagged

2

This is more of a PostgreSQL question rather than a PostGIS question, but sill a good one. Functions like ST_PixelAsPoints, ST_PixelAsPolygons, and ST_PixelAsCentroids return a set of composite record data type, which have several fields. This is very similar to ST_Dump, described here. To gain access to all the returned fields from these composite data ...


3

The function returns a table with typed columns (a schema) and rows (values). The .* at the end means "all columns from the table." It's a bit like SELECT * FROM X, but acts as syntactic sugar so you don't have to wrap the function call in a (SELECT * FROM function()).


0

I think I worked out the issue--I replaced the ArcGIS\Desktop10.2\bin\libpq.dll file with one for postgresql 9.3 (32-bit): http://get.enterprisedb.com/postgresql/postgresql-9.3.5-1-windows-binaries.zip I replaced it, restart ArcMap and I am now able to label.


1

From What I have found I think we can simply parse the xsd and find the column names.Since the column names are in any case going to be in string format.We only need to store that as varchar in the postgres table.


4

Your data looks like it's Mercator (big negative X's). I'll assume web mercator, but that could be wrong, and may lead to meter-level inaccuracies (if it's "real" mercator, use 3395). shp2pgsql -c -d -D -W LATIN1 -s 3857 -I 'path/to/shapefile.shp' my_table | psql -d mydb Now flip the coordinates to geographics inside the database ALTER TABLE my_table ...


0

EPGS 4269 is NAD83, not WGS84. Try changing the -s switch to 4326 instead. I'm not sure why the units are changing on you, both are decimal degrees.


1

All four of the databases you listed have the capability to support the type of query you're asking about. SQLite and Postgres through extensions (Spatialite & PostGIS respectively), mySQL and MariaDB have some basic GIS support built in natively. As to which to use, it depends on your needs. Personally I go with SQLite for simple in house ...


0

The problem was just that: I didn't load data with postgis with the SRID (4326 in my case). Then I've clone a geom column by creating a route table, changing the preceding geom column by the simple_geom column, with a special srid (linestring) that seems to be more comprehensible from the version 2.x of pgrouting. CREATE TABLE routes AS SELECT *, ...


0

Well, I did email the guys at US Census/TIGER and amazingly they did answer next day. In short, Chris W's observation is correct and they use USPS Zip to City as overrider even the TIGER data clearly points at a different Place(San Jose). I suspect some city boundaries have expanded however TIGER doesn't reflect it in the data file yet. Most likely TIGER ...


2

It looks like you're trying to get a unique integer field for QGIS to use as a key. I've had luck using the row_number window function for this. The basic syntax is: SELECT row_number() OVER () As vid, ... That will give each row a sequential integer starting with 1.


1

This is your issue: var flightPlanCoordinates = [ new google.maps.LatLng('<?php echo implode("','", $roads); ?>') ]; It does not create an array of LatLng. It creates an Array with one Invalid LatLng with in since it's expecting a latitude and a longitude and your giving it many latitudes and longitudes? If you have more ...


0

I don't understand your question very well. If you are asking how to display a line with the Google Maps API i think that you should check the documentation about Polyline or check the code samples like this one.


2

You should use osm2pgrouting to import the osm data into you DB, it's a command line tool that will automatically create the appropriate graph for pgrouting. http://www.pgrouting.org/docs/tools/osm2pgrouting.html


1

When you use OSM data for pgRouting, then you need to use special import tools. This will automatically solve your problem. To get started I highly recommend you the pgRouting workshop, because it will exactly guide you to get pgRouting work with OSM data: http://workshop.pgrouting.org/


0

It can be done in a single update, you just need to do some subqueries. UPDATE roads.rds_retaining_wall_register a SET length = c.sarea FROM ( SELECT refno, SUM(poly_area) sArea FROM gazetteer.lsg_special_designations b WHERE refno = (SELECT refno FROM gazetteer.lsg_special_designations WHERE gid = g) GROUP BY b.refno ) c WHERE ...


1

I would highly recommend a literature search before embarking on an IDW approach. A colleague of mine has a few papers on dual-phase sampling of cellular signals using Kriging. Because cellular signals are a highly nonstationary spatial process, an IDW approach is not appropriate. Whereas Kriging can have a model term to account for 2nd order effects as well ...


2

Finnish Airports Isochrones using the Interpolation plugin Site analyses can benefit greatly from using “drive-time” isochrones to define the study area. Drive time isochrones are often significantly different from simple buffer areas which disregard natural barriers such as rivers or slow roads. ...


1

You need to get the data from the server in geoJSON format. I can get you an example in PHP if you want, but I think there are plenty examples around. Then in Javascript, you can do something like this: $(document).ready(function(){ setInterval(function() { $.get("datasource.php", function (data) { if ( !data.error ) { ...


1

What are looking for is an OpenLayers strategy. You do not use this directly, but one of the subclasses, such as BBOX or Cluster or Fixed. The cluster strategy does as it says and combines point that are close together into clusters, based on a threshold, which will lead to much faster rendering times when you have many points in view. Unfortunately, the ...


2

I think the best approach is to go with Mapserver or Geosever (since you are new, Geoserver will look a lot easier) Once you set a WMS service with geoserver, you can add the resulting WMS to your OpenLayers project. Geoserver automatically returns only the information in the extent of the current view, also you can set the range of visible scales on your ...


3

So you're saying that you installed the PostGIS extension ? (CREATE EXTENSION postgis;) If so, you have to add a column geometry whith the following function: AddGeometryColumn(varchar table_name, varchar column_name, integer srid, varchar type, integer dimension, boolean use_typmod=true); So if your table is named myTable: SELECT ...


1

To expand on David Bitner's answer, here's an example ogr2ogr instruction demonstrating an optional OGR SQL clause to rename fields from a source dataset (shapefile in this case) before they are brought into a target dataset (a PostGREsql table): ogr2ogr -f "PostGreSQL" PG:"host=127.0.0.1 user=YourUser dbname=YourDB password=YourPass" ...


1

Let's assume that your 'deg' columns are +ve for North/East and -ve for South/West, and that all other metrics are +ve only, then making decimal degrees is straightforward. SELECT AddGeometryColumn('population', 'latlongeo', 4326, 'POINT', 2); UPDATE population SET latlongeo = ST_SetSRID(ST_MakePoint( sign(long_deg) * (abs(long_deg) + long_min/60 + ...


1

I tried your commands and changed filename raster, to filename char(250), , assuming you meant to use a string for that (and declaring it as a raster is a typo??). The commands seem to work without -C. With -C, I got some warnings/notices about numeric field overflow and no_data. But I guess that's just something in my data. What I tried is: echo ...


7

Why use a database? Because it's not necessarily the case, especially with larger datasets, that you can expect to be able to push the entire thing to the client. If you're talking thousands of points, then sure, but for millions of points you probably don't want each and every one represented in RAM on your end users' client. Not everyone has a super fast ...


0

Do you mean connecting the endpoint of a line to the startpoint? This should help http://postgis.net/docs/ST_AddPoint.html.


0

Thanks to Craig Ringer for suggestions. We don't actually need any procedural function to accomplish the task stated above (In fact it would be an ill-practice to prepare a procedural function for the same). The following SQL code is equally solving the problem. with x as ( SELECT seq, id1 AS node, id2 AS edge, cost, b.the_geom, b.source, b.target, ...


1

The file osm2po.config, which can be obtained from the downloads tab on the osm2po page, contains a table with four column, defined as: 1) concurrent order 2) class (1-127) 3) default speed in kmh 4) allowed transportation type (optional) - since v4.5.30 And here are some sample rows, which I think explain where the 12, 51, etc, you are ...


1

If you are into Java and want Flexibility, Graphhopper is an excellent alternative. Peter, the project lead, actively encourages extensions and changes and the community is very responsive. Graphhopper works differently to Pgrouting (I use both) and it is extremely fast. Instead of using a PostGis database, Graphhopper builds its graphs direct form the .pbf ...


1

I think the set of PostgreSQL drivers you downloaded are incomplete, and here's why: ArcGIS Server was installed as an x64 (bit) app, while the ArcGIS 10.2 Desktop client is an x86 (32-bit) app. So, referencing this ESRI documentation means the 64-bit postgreSQL libraries (libeay32.dll, libintl.dll, libpq.dll, and ssleay32.dll) go into the Server ...


6

A really easy, but not fantastic measure is to get the Hausdorff distance between each combination, which is done with the ST_HausdorffDistance function. Using approximate LineStrings from your figure, these are all shown in blue, and the Hausdorff distance is shown for one of the pairs of lines in red: And the query to sort the 6 combinations in ...


3

I don't have access to PostGres/PostGIS, but here is how I would go about it in ArcGIS (or other). Calculate the length of the original lines into a static column Buffer your lines according to how you define "similar". Do not dissolve buffers. Resulting buffers will have FID equal to original line. Intersect buffers and original lines. Resulting layer ...


0

This is how I got around it select * from polygon where st_disjoint( ( select polygon from polygons where id=1), neighbourhoods.polygon) =0 See more details here http://stackoverflow.com/questions/26241751/find-neighbouring-polygons-in-ruby


0

If your roads really consist of multi-geometries, then you will run into troubles with pgRouting. Then it's necessary to change your multi-linestrings into multiple regular linestrings. As already mentioned, you can use ST_Dump to achieve this. But in reality I have rarely seen real multi-linestrings even if the geometry type says so. This is because ...


0

ST_Startpoint can only be used for LINESTRING. http://postgis.net/docs/manual-2.1/ST_StartPoint.html Returns the first point of a LINESTRING geometry as a POINT or NULL if the input parameter is not a LINESTRING. You have multilinestrings and therefore you are getting nulls. See also discussion about the matter from this closed PostGIS ticket.


4

EDIT: Update, having belatedly realized this is a Map Server question. In general, to get the convex hull, you will want to group by id/gid, as producing the hull(s) is an aggregate operation. So, without the Mapserver part, the query would look like: SELECT id, ST_ConvexHull(ST_Collect(the_geom)) as the_geom from xyz group by id; Now, to get MapServer ...


2

I'm not sure a natural ordering really exists for a geometry, given that it is two-dimensional and can start from any point in its outer linear ring. However, to answer the second part of your question, the following will order by the left-most x coordinate of every geometry. Select * from island_table order by ST_XMin(ST_Envelope(geom)); EDIT: As pointed ...


0

If you are using postGIS 2.x, and all is installed and configured properly, you can also run shp2pgsql-gui from the bash. In the window dialog select the tab "Export": now you are able to extract a postGIS table to shapefile format.


2

The PostgreSQL Manual explains how to add the path to binaries to the PATH environment variable. This procedure depends on your OS distribution and how you installed PostgreSQL and/or PostGIS. Otherwise, if you want to run a program like shp2pgsql without adding it to PATH, the path to the binary needs to be specified: /usr/local/pgsql/bin/shp2pgsql ...


1

To investigate an error in a script file with psql, it may help to identify the first error by stopping the script on the first error with: \set ON_ERROR_STOP on This is off by default, which is why you might see repeated noise messages "ERROR: current transaction is aborted, commands ignored until end of transaction block" With an error like: ...


1

Try running the query in a terminal window or pgAdmin3 to see if it works there. If you want to visualize the routing query in QGIS look at the workshop: Create a database connection and add the “ways” table as a background layer. Add another layer of the “ways” table but select Build query before adding it. Then type the following into the SQL where ...


2

Figured this out! (also asked here) A record in a spatial table (Shapefile, PostGIS, Spatialite) that has no geometry can have geometry added by adding a 'part' to the feature using the advanced digitizing toolbar. Here's the workflow: Bring table containing the geometry-missing feature into QGIS Select the layer from the layers panel and start an edit ...


2

You need to cast from the output of ST_MAKE() which is a geometry to the geography data type. This casting is done by using the :: operator. The following command should work: UPDATE customer_campaign set geom=ST_MakePoint(customer_campaign.long, customer_campaign.lat)::geography;


2

You can also use a polygon to select the roads you want (in the top menu bar, the icon with the white arrow and the dotted lines). Then click on the layer containing the roads >> save as shapefile. If you make sure the box 'only save selected feautures' is thicked, you're new shapefile should only contain the selected roads.


0

If you have your data in PostGres/PostGIS, you can use the PostGIS pgsql2shp Dumper 4.4.2. Using the Dumper The pgsql2shp table dumper connects directly to the database and converts a table (possibly defined by a query) into a shape file. The basic syntax is: pgsql2shp [<options>] <database> [<schema>.]<table> pgsql2shp ...


5

If you don't have an attribute, you should first find a polygon feature class with the boundaries of Greece (e.g. on gadm.org). Then you have two solutions : You want the roads to be cut at the boundaries of you area of interest : use some clip tools (in QGIS : Vector -> Geoprocessing -> Clip ) You want to keep the two sides of a road when it crosses a ...


1

You need to also install postgis-scripts as included in the following, which solved the same issue for me: StackOverflow Answer


2

Here is a good tutorial that helped me a lot: http://pythongisbook.com/blog/2013/04/11/creating-a-postgresql-connection-from-a-qgis-layer-datasource/ update The above link has expired... :( Here is my code that I use in my QGIS plugin: from PyQt4.QtSql import * db = QSqlDatabase('QPSQL') if db.isValid(): # string db.setHostName('your_host') # ...


1

I do not believe that GDAL cares anything about the foreign key constraints and relational model. It has just made to handle one layer at a time. I would consider to copy your int4 primary keys into normal attributes and take care on the Spatialite side that they are unique over all your distinct databases. Once source data is in order you can append all ...


0

I've written a bottom-up hierarchical clustering algorithm, it has extra parameters that might not be useful to other users, but those should be easy to remove in implementation. First, create a new type to have point ids and geometries. CREATE TYPE pt AS ( gid character varying(32), the_geom geometry(Point)) and a type with cluster id CREATE TYPE ...



Top 50 recent answers are included