Hot answers tagged

5

Looks like there is an error in the statement at the link you provided, with the SRID being 94978, it should be: INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 4978, 'epsg', 4978, '', 'GEOCCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137.0,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","...


5

1) Add a new geometry column: ALTER TABLE mytable ADD COLUMN geom geometry(Point, 4326); 2) Update the new column with the geometry from your long and lat columns: UPDATE mytable SET geom = ST_SetSRID(ST_MakePoint(long, lat), 4326);


3

Not sure why you are getting that error, here is another option to add the column using psycopg2: import psycopg2 conn = psycopg2.connect("dbname = 'dbName' port = '5432' user = 'postgres' host = 'serverName' password = '********'") cur = conn.cursor() sqlQuery = "ALTER TABLE tableName ADD COLUMN newColumnName numeric;" cur.execute(sqlQuery) conn.commit() ...


3

Create a view CREATE VIEW v_location AS SELECT id, name, address, ST_AsGeoJSON (location) AS location FROM "locations"; Use the "SELECT *" on it: SELECT * FROM v_location where "locations"."id" = 2 limit 1


2

You have ~800 km east to west and ~1300 km north to south. This sounds like a perfect bounding box for the UK particularly if you include the islands to the north and south. Anything but meters (say feet, km, degrees) would be unreasonable. It is a so-so fit for Germany but could work. I think we have our linear units (m) but what system? Five digits, both ...


1

You might consiider PostGIS FDW (Foreign Data Wrapper) - it will connect to remote files such as Shapefiles or CSV, and they will appear and be queryable just like 'normal' PostGres tables. I think you'd need to use the GDAL/OGR FDW library - it supports shapefile connections. But don't take my word for it: http://postgis.net/2014/12/19/postgis_fdw/


1

The ellipse and double-quote should not be there and should read as: PSQL=${PGBIN}/psql The ellipse (...) you are seeing is not part of the actual text and is just telling you that there is more data not being shown. Your 'tiger.loader_platform.geocoder' field should end like this for os= 'sh': PSQL=${PGBIN}/psql SHP2PGSQL=${PGBIN}/shp2pgsql cd ${...


1

So just in case someone else encounters the same problem: this seems to work ! CREATE OR REPLACE FUNCTION drape(my_wkt text) RETURNS geometry AS $$ DECLARE geom3d geometry; BEGIN WITH line AS (SELECT my_wkt::geometry as geom), linemesure AS -- Add a mesure dimension to extract ...


1

ST_Dump will do it.... change DESC to ASC to get the other end. This example sorts the geometry by ST_Y: SELECT ST_Astext(geom) FROM ST_Dump('MULTIPOINT(1673118.56314624 5778263.30559826,1672914.41092247 5778129.131443,1672384.7731665 5778105.44570288,1673513.70724225 5778525.74090022)') Order By ST_Y(geom) DESC Limit 1 Or this example filters by path #:...


1

The reason that your current query is not returning anything is because you do not have the data there to complete the query that you want. I think that you are heading in the right direction, but you will need to have more data in your places table to properly realise the way the query will behave. You stated in your comments that you want to you want to ...



Only top voted, non community-wiki answers of a minimum length are eligible