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.


0

Probably in your table there are more than one type of geometry but, in a shape file, you must have only a type (Line, Polygon or Point). When you split lines the result could be not only a line but a point ( or multi). Use st_geometrytype(geom) for analyze what you have in your table. If you only need lines, you have just to delete what is not a linestring: ...


1

Boundless has a tool called GeoGig: http://geogig.org/ Users are able to import raw geospatial data (currently from Shapefiles, PostGIS or SpatiaLite) in to a repository where every change to the data is tracked. These changes can be viewed in a history, reverted to older versions, branched in to sandboxed areas, merged back in, and pushed to ...


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.


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.


2

Following functions exist in Oracle: SDO_CS.MAP_EPSG_SRID_TO_ORACLE SDO_CS.MAP_ORACLE_SRID_TO_EPSG So SELECT SDO_CS.MAP_ORACLE_SRID_TO_EPSG(xxx) FROM dual; yields: 90112 -> null 327680 -> 31300 8307 -> 4326 (Weird that 90112 isn't mapped...)


0

I think you got the query wrong. Try - SELECT ST_Transform(ST_Extent(geom), 3857) FROM nct_cov WHERE prop_name = 'SLATE';


0

OSM Bright from the MapBox people is also a useful starting point for custom OSM map tiles, unless you're trying to duplicate OSM carto exactly. OSM Bright is a little more lightweight (only 4 .mss style files, fewer layers defined), and easy to customize. Otherwise, as @math1985 said, just copy the .mml and .mss files from the openstreetmap-carto repo. ...


1

SELECT ST_X(pts.geom) as lon, ST_Y(pts.geom) as lat, poly.attribute FROM points pts, polygons poly WHERE ST_Intersects(poly.geom, pts.geom); where it is assumed that the attribute you want from the polgons table is called attribute. For non-trivial table sizes, you will want a spatial index on the geometry columns of polygons/points tables.


1

Select points.*, polygons.* from points inner join polygons on st_intersects(points.geom,polygons.geom);


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/


1

Figured it out. I needed to add in: map.aspect_fix_mode = mapnik.Map.ASPECT_RESPECT; before: map.extent = bbox;


0

Just checkout openstreetmap-carto in the Tilemill projects directory. The project.mml file, part of openstreetmap-carto, defines the queries and layers, so you won't need to do anything with that yourself.


2

There is an answer on StackOverflow that gives a mathematical, but not a Postgres answer. Translating to Postgres/Postgis, you could try something like this: select sum(st_x(pt)*weight)/sum(weight) as cx from weighted_points; select sum(st_y(pt)*weight)/sum(weight) as cy from weighted_points; Or if you want a point back, then, select st_makepoint(x.cx, ...


0

Using Fr├ęchet Mean as a basis for this, you could try something like the following SELECT EXP(SUM(LN(X)*W)/SUM(W)) weightedMedianX, EXP(SUM(LN(Y)*W)/SUM(W)) weightedMedianY, EXP(SUM(LN(X))/COUNT(*)) medianX, EXP(SUM(LN(Y))/COUNT(*)) medianY FROM PointTable


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 ...


10

'foo' is the name that has been assigned to the sub-selection. It has no function other than being syntactically necessary - a sub-selection in PostgreSQL must have a name assigned to it, but it does not matter what the name is. 'foo' was likely chosen because it is a common meaningless placeholder word used by programmers, similar to 'lorem ipsum' used by ...


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 ) { ...


2

The raster constraints are used to ensure that all rasters have the same SRID, pixel dimensions, pixel types and alignments, as you can see from RT_AddRasterConstraints docs. These are important if you want to do intersections, resampling, unions, reprojections, or vector-raster overlays, etc, as I'm sure you know. I was surprised that you could add an ...


1

If you are particularly interested in using meters as the unit for the search, you can use geography data types instead of geometry types. All geography based calculations return values in meters. Also, ST_DWITHIN is designed for this type of queries: select * from zones z where ST_DWITHIN(Geography(ST_Transform(z.geom,4326)), ...


0

So assuming that the raster is correctly loaded in PostGIS you can get your raster via readGDAL in R in the following way: library(raster) library(rgdal) dsn="PG:dbname='plots' host=localhost user='test' password='test' port=5432 schema='gisdata' table='map' mode=2" ras <- readGDAL(dsn) # Get your file as SpatialGridDataFrame ras2 <- raster(ras,1) # ...


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 ...


0

I think this might do the trick. Let's say you created your topology like this: SELECT topology.CreateTopology('my_topo', 4326, 0.01); SELECT topology.AddTopoGeometryColumn('my_topo', 'my_layer', 'topogeom', 'MULTIPOLYGON'); where my_layer is an existing table. Remove any data from tables with a TopoGeometry column using this topology. DELETE FROM ...


0

In my world, using a custom SRID (for Google Maps) something like this worked: SELECT * FROM addresses WHERE ST_DWithin(location, ST_SetSRID(ST_MakePoint(longitude, latitude), 3785), radius); where the type of location is a geometry(Point,3785), and longitude, latitude, and radius are floats (e.g. -100, 44, 30 for 100W/44N/30 "units" -- see below) See ...


3

Your Line ... WHERE ST_geometrytype(geom) = ST_Point; Indicates that ST_Point is a column since it is not in quotes. I see here also a comment from @Vince that is saying basically the same thing that I am. Since ST_Point is not quoted in your expression is is trying to evaluate what is in ST_geometrytype(geom) against another field/column and it can't ...


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 ...


2

Lets assume you want to save a feature from a OpenLayers Vector Layer into PostGis and that you want to achieve this using GeoServer. The general steps would be as follows: You need to configure a WFS in geoserver using PostGis as a data store (GeoServer publish posgis table) You need to configure your vectyor layer to point to the wfs you just created ...


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 + ...


2

Since you would "prefer everyone on ArcGIS, using ArcSDE, but ... don't have the budget" you may be interested in "GISquirrel" http://www.gisquirrel.com which gives multi-user editing of PostGIS tables in ArcMap (at Basic licence level). This may give you what you want for all users, but if you still need QGIS in the mix then you are quite right to consider ...


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 ...


2

You can use the write function of the OpenLayers.Format.GeoJSON to generate (Geo)JSON directly. The input is either an OpenLayers geometry, feature or feature vector, so the returned JSON, will either contain one or many features depending on the input. Note if your OpenLayers Features have attributes, these get turned into JSON too, so you can use this to ...


3

Let me respectfully disagree, but I don't think that the Hausdorff distance function is the appropriate way to snap a GPX track to a street network. Rather you should be looking into methods for map matching, e.g. Different approaches for map matching : links / ideas?


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.


1

I don't know the schema of the OSM tables, but you asked for a query like this: DELETE FROM labels WHERE label_id IN ( SELECT label_id_2 FROM ( SELECT a.label_id AS label_id_1, b.label_id AS label_id_2 FROM labels AS a, labels AS b WHERE STDWithin(a.the_geom,b.the_geom, 0.001) and a.street = b.street and a.label_id != b.label_id ) ...


1

The "paths" they are referring to are the program executables (*.exe) for your system, as mentioned in step 4. For example, if you are using 7-zip for unzipping data, you would replace: <unzip_command> with "C:\Program Files (x86)\7-Zip\7z.exe" in the SELECT portion of the SQL statement. You could make this easier by creating SQL variables ...


1

I agree with you that the instructions in this step are unclear. My interpretation is that you run this command, then edit the value of the declare_sect column in the loader_platform table that's created by the command. The value of the declare_sect column rows should be the path to the os commands listed (e.g. pg, unzip, etc...).


0

The proxy works fine. The problem was at this code line: xtype: "id", header: "# of Facilities", dataIndex: "id", format: "1,000", align: "right", width: 55, editor: { xtype: "numberfield" } xtype value have to be allowed feature (numbercolumn). ...


0

The previous answer has an invalid xmlns. I think the correct one will be: <?xml version="1.0" encoding="UTF-8"?> <kml xmlns="http://www.opengis.net/kml/2.2"> <Document> <Style id="0"> </Style> <Placemark> <Polygon> <outerBoundaryIs> <LinearRing> <coordinates> ...


0

I'd like to convert the raster data to points, get the lat/lon values of each of those points, and find the closest data point to each city This goal is actually easier if you keep the data in raster form and use Extract Values to Points (note: Spatial Analyst extension required). If you create a set of points for each city you are interested in, then ...


1

From the ArcGIS Help Site, Raster To Point(Conversion), it lists all the valid arguments you are looking for. Also, for checking on the SRID of your .tif file, right click the file from Catalog and click properties. This should bring up Raster Dataset Properties, scrolling down you should see a Spatial Reference section.


1

Which version of QGIS are you running? I used to encounter this all the time, but haven't since switching to QGIS 2.4. I suspect it's been fixed. One workaround which I used to use was that instead of closing QGIS you can use pgAdmin to just temporarily terminate the open blocking connection from QGIS. Go to Tools -> Server Status, select the connection ...



Top 50 recent answers are included