Re: [postgis-users] Non GIS uses of PostGIS: Any tips?

[James Keener]

I believe the 3rd dimension is sometimes used as time (instead of elevation).  
Example:

> 5.19. Trajectory Functions Abstract

> These functions support working with trajectories. A trajectory is a linear 
> geometry with a measure (M value) on each coordinate. The measure values must 
> increase along the line. Spatio-temporal data can be modelled by using 
> relative times (such as the epoch) as the measure values.

https://postgis.net/docs/manual-3.1/reference.html

You might be able to use QGIS to help visualize this, though I haven't used it 
with 3d data, so I can't provide any more concrete guidance on that front.

Jim

On May 23, 2022 2:17:28 AM EDT, Rory Meyer  wrote:
>Afternoon everyone,
>
>I'm assisting a PhD student with their work and I think PostGIS could provide 
>some interesting tools. The problem is that it's using spatial data in a non 
>GIS way.
>
>The student is monitoring the settling behaviour of oyster sprats (larvae) to 
>determine the natural features that lead up to oyster reefs. The experiment is 
>to have a bunch of sprats in a petri dish, simulate a predator or existing 
>oyster reef using chemicals, and to measure the settlement behaviour over 
>several hours using a webcam mounted above the petri-dish. The initial steps 
>would be to use computer vision tools to get an (x,y,t) vector for each oyster 
>sprat, or movement segment, for the duration of the experiment. Using PostGIS 
>functions to build trajectories, calculate average/min/max speed per segment, 
>see if there are any intersections between multiple sprats, possibly map 
>trajectories to behaviours. I'm not sure what else, I'm not a biologist.
>
>My question is how do you do this with a non-spatial SRID? Can I just use 
>pixel locations as X,Y fields, set the SRID to 0 and get pixels/sec out of any 
>ST_funcs? Is there some previous work done on creating a camera specific SRID 
>(probably a bit overkill) to take into account distortions and transform 
>pixel/sec to meter/sec?
>
>Regards,
>Rory

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] OSM and US DEM in PostGIS

[James Keener]

Can you explain the issue you're having and what you've tried? I've loaded
the DEM data into postgis before and have been able to query it and use it
in QGIS.

Jim

On Sun, Jun 20, 2021 at 3:57 PM Siddarth Madala  wrote:

> I was looking for some assistance with the following problem. My use case
> of PostGIS is to store the United States' OpenStreetMap (OSM) data. This is
> a solved problem with a significant amount of documentation available.
> However, my use case differs in that I want to add US DEM elevation rasters
> to the database to enable my application to query and receive a graph
> seeded with these elevation values depending on my users' location. This
> problem has almost no good documentation, and most solutions are outdated
> or fail on my test server. Other services that provide elevation on demand
> are either too costly or do not provide a small enough resolution for my
> use case. Thus, I hope that anyone can aid me in the setup and deployment
> on this bespoke database.
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] PostGIS + pgdump

[James Keener]

It'd help if you told us what errors you're getting.

Jim

On Thu, May 16, 2019 at 9:10 AM Zwettler Markus (OIZ) <
markus.zwett...@zuerich.ch> wrote:

> Hi,
>
>
>
>
>
> We did a default PostGIS installation within a PostgreSQL 9.6 database:
>
>
>
> ===
>
> create extension if not exists postgis;
>
> create extension if not exists postgis_topology;
>
> create extension if not exists ogr_fdw;
>
> create extension if not exists pgrouting;
>
> ===
>
>
>
> This installed PostGIS within the public schema of the database.
>
> (SET SCHEMA is not supported since V2.3 anymore)
>
>
>
> Our customer application was also installed within the public schema.
>
>
>
> When we pg_dump + pg_restore the database we got a lot of errors.
>
>
>
> We tried to pg_restore the dump into a database without postgis extension
> => postgis errors
>
> We tried to pg_restore the dump into a database with postgis extension =>
> postgis errors
>
> We tried to pg_restore only the public schema into a database with postgis
> extension => postgis errors
>
>
>
> QUESTION:
>
> How does the PostGIS extension have to be installed so that pg_dump +
> pg_restore DO NOT error out?
>
>
>
>
>
> Thanks,
>
> Markus
>
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Calculating driving distance/times

[James Keener]

I actually just finished talking about this within the context of a
transit stop's walkshed.

http://jimkeener.com/posts/calculating-service-area-part-2-defining-the-walkshed

I used PostgreSQL + postGIS + pg-routing to do analysis and QGIS to do
the visualization.

Hop that helps some!

On 1/28/19, Anthony DeBarros  wrote:
> Hi, everyone. Longtime lurker, first-time poster here ...
>
> Let's say I have a collection of several thousand U.S. locations in the
> U.S. geocoded by lat/long.
>
> I'd like to perform an analysis that lets me visualize places in the U.S.
> that have none of these locations within a certain driving distance. For
> example, if I had a collection of locations of fast-food restaurants, I
> would like to show all the places in the U.S. that have no fast-food within
> 100 miles.
>
> Are there tools PostGIS, QGIS, others or methodologies you'd recommend?
>
> Thanks,
> Anthony
>
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] a question about post gis

[James Keener]

Are you asking how to fill a polygon with circles of a fixed radius? I'm
not sure there is in general a unique solution for that or that PostGIS is
the best tool for the job. I'm sure someone could make postgis figure it
out, but I can't imagine a clean way of doing it (in an optimal) fashion,
and again, I don't think it has a unique solution.

On Wed, Dec 5, 2018 at 5:53 PM Mahdi Kalhor  wrote:

> Hello.
> I have read the documentation of post gis and i could not find the answer
> of my question. I want to pass a polygon to post gis and get the list of
> circles with radius=500 meters which can fill that polygon. I need to find
> the center of those circles. I need this for educational purpose for one of
> my papers. Any help is appreciated.
>
>
>
>
> Sincerely Yours
> Mahdi Kalhor
> Phd candidate in Urban Planning
> The Inventor Of GIS Dongle.
>
> my website: www.mahdikalhor.ir 
>
>
>
> *Sleep tight Xerxes The Great. Your sons promise to revive *
> *Perspolis again, but not physically which can be burnt by *
> *another **Alexander, rather scientifically, which resurrects your *
>
>
>
> *name forever !!!*
> *"Try to be as generous as rain which fills all the empty bowls *
> *and never asks 'whose bowls are these?' " *
> *((Cyrus The Great)) *(Reference: Xenophon's Cyrus the Great: The Arts of
> Leadership and War,Xenophon)
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Hi hi hello - Postgis & postgres binary

[James Keener]

I'm not sure what you mean by an embedded database. PostgreSQL isna stand alone 
database, not an embeddable one.

If you're talking about building a docker container, what base are you using? 
Can you use one that has access to or install more repositories?

Jim

On November 14, 2018 7:24:09 AM EST, "Michał Garnysz" 
 wrote:
>I'm struggling to create working instance of postgres with postgis
>enabled
>database
>i need to have one binary file i will use as emmbedded database where i
>need call
>CREATE EXTENSION postgis;
>
>I tried many approaches and usually im ending up with dependency to
>container where i installed postgis or issue with unable to locate
>$libdir:postis2.5
>(other are openssl v.1.1.0 on centos/redhat)
>
>Is there any place i can get DB instance with already copiled binaries
>vs 1
>or 2 version?
>
>Hope someone have any advice/ hint im stuck on this for to long.
>
>Best regards Michal

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] add group number to a group by clause

[James Keener]

You could try something with a window function. Something like case when
lag(x) = x then y else y + 1 end

On Thu, Jul 20, 2017 at 1:53 PM, Olivier Leprêtre 
wrote:

> Hi,
>
>
>
> I have sets of points which are piled up by groups. I found how to show
> the different groups with the query below but I didn't find how to add a
> group number for each group. I tried with "over partition" but it seems
> that it's not possible to partition with geom column.
>
>
>
> Here is the query :
>
>
>
> select o.nbre,d.code,d.numero,d.nomvoie,d.commune from
>
> (select geom,count(*) as nbre from adress group by geom having count(*)
> >1) as o,
>
>  lateral (select * from adress) as d where st_within(o.geom,d.geom)
>
>
>
> it returns the piled points preceded with piled points count.
>
>
>
> 3 pointa (3 piled points)
>
> 3 pointf
>
> 3 pointg
>
> 2 point1 (2 pp)
>
> 2 point2
>
> 4 pntw   (4 pp)
>
> 4 pntx
>
> 4 pnty
>
> 4 pntz
>
>
>
> How can I add a group column like this ?
>
>
>
> 13 pointa
>
> 13 pointf
>
> 13 pointg
>
> 22 point1
>
> 22 point2
>
> 34 pntw
>
> 34 pntx
>
> 34 pnty
>
> 34 pntz
>
>
>
>
>
> Thanks for any idea,
>
>
>
> Olivier
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> 
>  Garanti
> sans virus. www.avast.com
> 
> <#m_-1405050486293552671_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] PostGIS Raster - Loading MrSID format

[James Keener]

I wrote something on this a while ago. Hope it helps!

http://jimkeener.com/posts/orthoimagery

On Mon, Apr 10, 2017 at 11:45 PM, Osahon Oduware  wrote:
> Hi All,
>
> I am trying to use the raster2pgsql tool to import raster images in MrSID
> format to PostGIS database on a Windows system. However, I get an error
> indicating that the format is not supported which I could confirm by
> running:
> raster2pgsql -G
>
> Please, could someone help with the basic steps to follow to load raster
> data in MrSID format using the raster2pgsql tool.
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] IFC to PostGIS

[James Keener]

Sorry, I don't have a windows box or dev tools. Hopefully someone else
can track down the issue for you.

Best of luck,
Jim

On Tue, Feb 21, 2017 at 9:32 AM, Frans Knibbe  wrote:
> Hi James,
>
> I am on Windows 7. Upon clicking on the executable (ifc2Pg.exe), I see a
> splash screen and soon after the message "Ifc2Pg has stopped working". No
> further information is presented. The Windows event viewer tells me "The
> process was terminated due to an unhandled exception.". The RAR file that I
> downloaded does not seem to contain source code, nor can I find source code
> online.
>
> Regards,
> Frans
>
> On 21 February 2017 at 15:12, James Keener  wrote:
>>
>> How does it crash, and is there source available for it?
>>
>> On Tue, Feb 21, 2017 at 9:08 AM, Frans Knibbe 
>> wrote:
>> > Hi,
>> >
>> > I am looking for ways to import Industry Foundation Classes (IFC) data
>> > to
>> > PostGIS. FME can do it, but I wonder if there are free/open source
>> > alternatives.
>> >
>> > A search through the archives of this list led me to IFC2PG, which is
>> > described here (in French). The page contains a link to the IFC2PG
>> > software,
>> > but unfortunately it crashes when I try to start it.
>> >
>> > Does anyone know of a way to get IFC2PG to work? Or of another way to
>> > load
>> > IFC into PostGIS?
>> >
>> > Thanks,
>> > Frans
>> >
>> > ___
>> > postgis-users mailing list
>> > postgis-users@lists.osgeo.org
>> > https://lists.osgeo.org/mailman/listinfo/postgis-users
>> ___
>> postgis-users mailing list
>> postgis-users@lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] IFC to PostGIS

[James Keener]

How does it crash, and is there source available for it?

On Tue, Feb 21, 2017 at 9:08 AM, Frans Knibbe  wrote:
> Hi,
>
> I am looking for ways to import Industry Foundation Classes (IFC) data to
> PostGIS. FME can do it, but I wonder if there are free/open source
> alternatives.
>
> A search through the archives of this list led me to IFC2PG, which is
> described here (in French). The page contains a link to the IFC2PG software,
> but unfortunately it crashes when I try to start it.
>
> Does anyone know of a way to get IFC2PG to work? Or of another way to load
> IFC into PostGIS?
>
> Thanks,
> Frans
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] help with a query

[James Keener]

Depends on what you mean by direction. If you want to grab the start and end 
points (st_startpoint and st_endpoint) and check their x and y (st_x and st_y) 
for some condition (both less at the end?) Then update the record with the 
value of st_reverse.

I guess my other question is why it matters.

Jim

On September 13, 2016 8:31:07 AM EDT, Jonatan Malaver 
 wrote:
>Hello,
>
> I'm trying to come up with a query that would check the direction of a
>line. If the end point is not the start point of the next line to
>update
>the line by reversing that line. Can anyone give me pointers on how to
>do
>it?
>
>Thanks,
>Jon
>
>
>
>
>___
>postgis-users mailing list
>postgis-users@lists.osgeo.org
>http://lists.osgeo.org/mailman/listinfo/postgis-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] GRANTable Row Permissions

[James Keener]

I messed up the list! Thanks!

On July 4, 2016 7:39:56 PM EDT, Regina Obe  wrote:
>I think this question may be better asked on pg-general since your
>focus is more on RLS than working with PostGIS.
>
>
>
>-Original Message-
>From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On
>Behalf Of James Keener
>Sent: Saturday, July 02, 2016 1:51 AM
>To: PostGIS Users Discussion 
>Subject: [postgis-users] GRANTable Row Permissions
>
>I'm trying to work out how to grant permissions to rows in a table
>without having to rebuild the pg auth mechanisms (see below). One
>option is to have many tables (each representing a row), and grant
>normally.
>The other is, like I build below, uses a table and a recursive CTE to
>resolve the PG group membership and apply it to the table in question
>using a RLS policy.  Is any of this sane?
>
>So, aay I have
>
>create table viz (
>viz_id bigserial primary key,
>name text
>);
>
>create role group_a;
>create role group_b;
>create role user1;
>create role user2;
>create role user3;
>
>grant group_a to user1;
>grant group_b to group_a;
>
>insert into viz (name) values ('test 1'),('test 2'),('test 3');
>
>
>
>I am trying to find a way to essentially do the following:
>
>   revoke select on viz from public;
>   grant select on viz to group_a where viz_id = 1;
>   grant select on viz to user2 where viz_id = 2;
>   grant select on viz to group_b where viz_id = 3;
>
>With RLS I can create a policy that can validate via an arbitrary sql
>statement, but I can't think of a clean way to have row-level grants
>that can be implemented without having to kludge the pg permission
>system into a table.  The following kind of gets at what I want, but
>uses a table instead of being able to grant.
>
>create table viz_perm (
>viz_id bigint references viz,
>role_name text,
>can_view boolean not null default false
>);
>
>alter table viz enable row level security;
>alter table viz_perm enable row level security;
>
>create policy viz_permissions on viz_perm for select using (
>(with recursive rec_roles(grantee,granted) as (
>select roless.rolname as grantee, groupss.rolname as granted
>from pg_roles roless
>inner join pg_auth_members
>on roless.oid = pg_auth_members.member
>inner join pg_roles groupss
>on groupss.oid = pg_auth_members.roleid
>union
>select rec_roles.grantee as grantee, groupss.rolname as granted
>from rec_roles
>   inner join pg_roles roless on roless.rolname = rec_roles.granted
>inner join pg_auth_members
>on roless.oid = pg_auth_members.member
>inner join pg_roles groupss
>on groupss.oid = pg_auth_members.roleid
>)
>select bool_or(true)
>from rec_roles
>where
>role_name = current_user
> or (grantee = current_user and granted = role_name))
>);
>
> create policy viz_permissions on viz using (
> (select bool_or(can_view)
>  from viz_perm
>  where viz_perm.viz_id=viz.viz_id)
> );
>
>insert into viz_perm (viz_id, role_name, can_view) values
>(1, 'group_a', true),
>(2, 'user2',   true),
>(3, 'group_b', true);
>
>grant select on viz to user1;
>grant select on viz_perm to user1;
>grant select on viz to user2;
>grant select on viz_perm to user2;
>
>
>set role user1;
>select * from viz;
>-- viz_id |  name
>--+
>--  1 | test 1
>--  3 | test 3
>--(2 rows)
>
>reset role;
>set role user2;
>select * from viz;
>-- viz_id |  name
>--+
>--  2 | test 2
>--(1 row)
>
>reset role;
>
>While the above more-or-less works, it feels very wonky.  Is there a
>better way to do this? Would it be better to have a table for each viz,
>necessitating each table having a single row, and using the standard
>permission system.  Is what I describe and build in this email an
>acceptable way to go about doing what I want to do?
>
>Thanks,
>Jim
>___
>postgis-users mailing list
>postgis-users@lists.osgeo.org
>http://lists.osgeo.org/mailman/listinfo/postgis-users
>
>___
>postgis-users mailing list
>postgis-users@lists.osgeo.org
>http://lists.osgeo.org/mailman/listinfo/postgis-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

[postgis-users] GRANTable Row Permissions

[James Keener]

I'm trying to work out how to grant permissions to rows in a table
without having to rebuild the pg auth mechanisms (see below). One option
is to have many tables (each representing a row), and grant normally.
The other is, like I build below, uses a table and a recursive CTE to
resolve the PG group membership and apply it to the table in question
using a RLS policy.  Is any of this sane?

So, aay I have

create table viz (
viz_id bigserial primary key,
name text
);

create role group_a;
create role group_b;
create role user1;
create role user2;
create role user3;

grant group_a to user1;
grant group_b to group_a;

insert into viz (name) values ('test 1'),('test 2'),('test 3');



I am trying to find a way to essentially do the following:

revoke select on viz from public;
grant select on viz to group_a where viz_id = 1;
grant select on viz to user2 where viz_id = 2;
grant select on viz to group_b where viz_id = 3;

With RLS I can create a policy that can validate via an arbitrary sql
statement, but I can't think of a clean way to have row-level grants
that can be implemented without having to kludge the pg permission
system into a table.  The following kind of gets at what I want, but
uses a table instead of being able to grant.

create table viz_perm (
viz_id bigint references viz,
role_name text,
can_view boolean not null default false
);

alter table viz enable row level security;
alter table viz_perm enable row level security;

create policy viz_permissions on viz_perm for select using (
(with recursive rec_roles(grantee,granted) as (
select roless.rolname as grantee, groupss.rolname as granted
from pg_roles roless
inner join pg_auth_members
on roless.oid = pg_auth_members.member
inner join pg_roles groupss
on groupss.oid = pg_auth_members.roleid
union
select rec_roles.grantee as grantee, groupss.rolname as granted
from rec_roles
inner join pg_roles roless on roless.rolname = rec_roles.granted
inner join pg_auth_members
on roless.oid = pg_auth_members.member
inner join pg_roles groupss
on groupss.oid = pg_auth_members.roleid
)
select bool_or(true)
from rec_roles
where
role_name = current_user
 or (grantee = current_user and granted = role_name))
);

 create policy viz_permissions on viz using (
 (select bool_or(can_view)
  from viz_perm
  where viz_perm.viz_id=viz.viz_id)
 );

insert into viz_perm (viz_id, role_name, can_view) values
(1, 'group_a', true),
(2, 'user2',   true),
(3, 'group_b', true);

grant select on viz to user1;
grant select on viz_perm to user1;
grant select on viz to user2;
grant select on viz_perm to user2;


set role user1;
select * from viz;
-- viz_id |  name
--+
--  1 | test 1
--  3 | test 3
--(2 rows)

reset role;
set role user2;
select * from viz;
-- viz_id |  name
--+
--  2 | test 2
--(1 row)

reset role;

While the above more-or-less works, it feels very wonky.  Is there a
better way to do this? Would it be better to have a table for each viz,
necessitating each table having a single row, and using the standard
permission system.  Is what I describe and build in this email an
acceptable way to go about doing what I want to do?

Thanks,
Jim
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] perform intersection on large tables

[James Keener]

Is there a reason you don't simply join and insist on looping? Index the 
geometry fields and joining on st_contains or something should have decent 
performance.

Jim

On September 2, 2015 2:32:40 PM EDT, Travis Kirstine  
wrote:
>I'm trying to perform an intersection using two tables, one table
>contains
>a regular grid of polygon geometries the other table contains parcels
>polygons.  I need to perform an intersection to extract the parcels as
>lines with a label point for each polygon in the grid table.  My novice
>approach was to create new table with a generic geometry type and then
>loop
>through each row in the grid table to run the intersection against the
>parcels and insert the results into the table.
>
>The approach works OK when dealing with a few records but fails
>miserably
>when run against larger tables
>
>Any suggestions
>
>
>   CREATE TABLE results (
>id SERIAL,
>pin VARCHAR(9),
>zone VARCHAR,
>base_name VARCHAR(9)
>);
>SELECT AddGeometryColumn('results', 'geom', 4326, 'GEOMETRY', 2 );
>
>
>CREATE OR REPLACE FUNCTION genTiles() RETURNS int4 AS '
>DECLARE r RECORD;
>BEGIN
>
>FOR r IN SELECT * FROM grid_table
>LOOP
>INSERT INTO results (zone, base_name, pin, geom)
>SELECT
>r.zone, r.base_name,
>p.pin,
>ST_Intersection((ST_Dump(ST_Boundary(p.geom))).geom,
>r.geom) AS geom
>FROM
>parcel p
>WHERE
>ST_Intersects(p.geom, r.geom);
>
>
>   INSERT INTO results (pin, zone, base_name, geom)
>SELECT
>r.zone, r.base_name,
>p.pin,
> ST_Intersection((ST_Dump(ST_PointOnSurface(p.geom))).geom,
>r.geom) AS geom
>FROM
>parcels p
>WHERE
>ST_Intersects(ST_PointOnSurface(p.geom), r.geom);
>
>END LOOP;
>return 1;
>END;
>' LANGUAGE plpgsql;
>
>SELECT genTiles() as output
>
>
>
>
>___
>postgis-users mailing list
>postgis-users@lists.osgeo.org
>http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] California Shapefile

[James Keener]

With TIGER there are a few things you need.  The ADDR and EDGES records
can be used find the zipcode for an address, and geocoding. I'm not sure
why you need an altitude, you'll have to use other sources, DEM maybe?,
or if lon and alt will be unique enough?

http://www2.census.gov/geo/pdfs/maps-data/data/tiger/tgrshp2014/TGRSHP2014_TechDoc_Ch3.pdf

PostGIS has a geocoder that can work on the TIGER data:
http://postgis.net/docs/Geocode.html

Jim


On 06/10/2015 02:16 AM, Kevin Zhao wrote:
> Hi everyone,
> 
> I am new to PostGis and just finished the tutorial, loaded nyc data into
> my Postgresql database.  I am trying to build a database where I can
> give it street address or longitude and altitude, then it gives me a zip
> code back.  All for California only.  I know that I need shapefiles, but
> I looked online, found a few sites(including
> http://www.census.gov/geo/maps-data/data/tiger-line.html), and nothing
> really stood out to me as the shapefile that I am looking for.  Could
> someone give an idea on where I can find the shapefile that will fit my
> needs?
> 
> Thank you so much!
> 
> Kevin
> 
> -- 
> Kevin Zhao | LinkedIn 
> (434) 282-5369
> Business Analytics and Information Technology | University of Virginia 2016
> President | Project M 
> Alpha Kappa Psi Professional Business Fraternity
> 
> External Vice President | Mainland Student Network
> 
> 
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> 



signature.asc
Description: OpenPGP digital signature
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Beginning in PostGIS

[James Keener]

OH, I think I understand: You want to copy and paste from a shapefile
layer into the appropriate postgis layer?

I don't know if you can do that directly.  You may want to write a
script to do it, or import the shapefile into a temporary table and then
using multiple sql statements to build up your normalized tables.

http://stackoverflow.com/questions/1310103/split-table-with-duplicates-into-2-normalized-tables

has a little example.  Basically you import the towns with a insert into
blah select distinct (town code, town description) and then continue
that, but for the foreign keys have your updates or inserts do a
subquery to find the correct foreign row.

Jim

On 04/18/2015 02:01 PM, Luciano wrote:
> Ok,
> 
> I have a shape file with the following structure.
> 
> Shape file, columns:
> town ​​code;
> town description;
> Neighborhood code;
> name of the neighborhood;
> block code;
> Street code;
> street name;
> 
> Using the How to Copy / paste a geometry fromshape file to postgiswith
> follow structure:
> 
> Cities table (data):
> - Town id
> - Description of town
> 
> Neighborhoods table (data):
> - Id of the neighborhood
> - Description of the neighborhood
> - Id of town (foreign key)
> 
> Blocks table:
> - Id of the court
> - Block of code
> - Town id (foreign key)
> - Geometry, polygon
> 
> Streets table:
> - Street id
> - Street name
> - Town id (foreign key)
> - Geometry, line
> 
> 
> 
> 2015-04-18 13:36 GMT-03:00 Rémi Cura  >:
> 
> maintaining your data model is easy to do with triggers.
> 
> About copy / past.
> Sorry this just doesn't make much sense to me.
> 
> You can import shapefile into postgres if it is the question.
> You can also use trigger on the import table to fill you rmodel with
> the imported data.
> 
> Cheers,
> Rémi-C
> 
> 2015-04-18 17:39 GMT+02:00 Luciano  >:
> 
> 
> Hi,
> I wonder how can I update a postgresql postgis database before
> the following scenario:
> Always worked with shape files and update them used copy / paste
> between files.
> Now, think about creating a database in PostgreSQL and would
> like to continue using copy / paste to update polygons, but in
> my database structure is different from the shape file. For example:
> Imagine that the shapefile have all the fields in one table,
> already in the database, by reason of standardization, have
> these columns in tables
> distinct. Below is an example of a register of towns.
> 
> File shape, columns:
> town ​​code;
> town description;
> Neighborhood code;
> name of the neighborhood;
> block code;
> Street code;
> street name;
> 
> In Postgres / Gis could look like this:
> 
> Cities table (data):
> - Town id
> - Description of town
> 
> Neighborhoods table (data):
> - Id of the neighborhood
> - Description of the neighborhood
> - Id of town (foreign key)
> 
> Blocks table:
> - Id of the court
> - Block of code
> - Town id (foreign key)
> - Geometry, polygon
> 
> Streets table:
> - Street id
> - Street name
> - Town id (foreign key)
> - Geometry, line
> 
> How could update (insert) a block in postgresql table using copy
> / paste the shape file?
> Would have to create a trigger/procedure (instead of) to
> automate the process?
> Fields of shape file should be equal to the fields of database
> table?
> Some practical example as a reference?
> 
> tia
> -- 
> Luciano
> 
> 
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org 
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> 
> 
> 
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org 
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> 
> 
> 
> 
> -- 
> Luciano
> 
> 
> 
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> 



signature.asc
Description: OpenPGP digital signature
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Beginning in PostGIS

[James Keener]

I guess I'm still not fully understanding the problem. I don't understand what 
problem the normalization is causing you. You shouldn't need to duplicate the 
rows in different tables when you duplicate one in another table.

To edit fields in QGIS you need to enable editing on the layer and then you can 
get end editable form for each feature or you can edit directly in the 
attribute table. Copy and pasting features in QGIS copied all of the attributes 
as well.

Can you give a more complete example of the issue you're facing?

Jim

Jim

On April 18, 2015 12:11:38 PM EDT, Luciano  wrote:
>Yes, I'm using QGIS. I agree, if I make a table in the database with
>the
>same structure the shape file is simple. The copy / paste works
>perfectly.
>But my question is how to update for example the blocks table, using
>the copy
>/ paste, since the database structure is different.
>For example, if I copy a polygon layer shape, and try to stick to the
>database layer, the fields of the new polygon will be void.
>Note that my database blocks table does not have the same structure of
>the shape
>file because it is normalized (or should be), so the fields of two data
>sources do not match.
>In this case, what is the best practice?
>
>tia
>
>2015-04-18 12:44 GMT-03:00 James Keener :
>
>> tl;dr: Have you tried QGIS?
>>
>> What were you using to copy/paste before?  I didn't think straight
>> editing of the DBaseIII files directly was a sane thing to do, as
>> they're linked up with the shape and shape-index files.
>>
>> PostGIS is just a PostgreSQL database, so any editor that can allow
>you
>> to edit/duplicate PostgreSQL tables could work.  As for mutating
>> geometries, maybe QGIS?  That would also allow you to edit
>geometries,
>> attributes, as well as duplicate features.
>>
>> Hope that helps,
>> Jim
>>
>> On 04/18/2015 11:39 AM, Luciano wrote:
>> >
>> > Hi,
>> > I wonder how can I update a postgresql postgis database before the
>> > following scenario:
>> > Always worked with shape files and update them used copy / paste
>between
>> > files.
>> > Now, think about creating a database in PostgreSQL and would like
>to
>> > continue using copy / paste to update polygons, but in my database
>> > structure is different from the shape file. For example:
>> > Imagine that the shapefile have all the fields in one table,
>already in
>> > the database, by reason of standardization, have these columns in
>tables
>> > distinct. Below is an example of a register of towns.
>> >
>> > File shape, columns:
>> > town ​​code;
>> > town description;
>> > Neighborhood code;
>> > name of the neighborhood;
>> > block code;
>> > Street code;
>> > street name;
>> >
>> > In Postgres / Gis could look like this:
>> >
>> > Cities table (data):
>> > - Town id
>> > - Description of town
>> >
>> > Neighborhoods table (data):
>> > - Id of the neighborhood
>> > - Description of the neighborhood
>> > - Id of town (foreign key)
>> >
>> > Blocks table:
>> > - Id of the court
>> > - Block of code
>> > - Town id (foreign key)
>> > - Geometry, polygon
>> >
>> > Streets table:
>> > - Street id
>> > - Street name
>> > - Town id (foreign key)
>> > - Geometry, line
>> >
>> > How could update (insert) a block in postgresql table using copy /
>paste
>> > the shape file?
>> > Would have to create a trigger/procedure (instead of) to automate
>the
>> > process?
>> > Fields of shape file should be equal to the fields of database
>table?
>> > Some practical example as a reference?
>> >
>> > tia
>> > --
>> > Luciano
>> >
>> >
>> >
>> > ___
>> > postgis-users mailing list
>> > postgis-users@lists.osgeo.org
>> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>> >
>>
>>
>> ___
>> postgis-users mailing list
>> postgis-users@lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>
>
>
>-- 
>Luciano
>
>
>
>
>___
>postgis-users mailing list
>postgis-users@lists.osgeo.org
>http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Beginning in PostGIS

[James Keener]

tl;dr: Have you tried QGIS?

What were you using to copy/paste before?  I didn't think straight
editing of the DBaseIII files directly was a sane thing to do, as
they're linked up with the shape and shape-index files.

PostGIS is just a PostgreSQL database, so any editor that can allow you
to edit/duplicate PostgreSQL tables could work.  As for mutating
geometries, maybe QGIS?  That would also allow you to edit geometries,
attributes, as well as duplicate features.

Hope that helps,
Jim

On 04/18/2015 11:39 AM, Luciano wrote:
> 
> Hi,
> I wonder how can I update a postgresql postgis database before the
> following scenario:
> Always worked with shape files and update them used copy / paste between
> files.
> Now, think about creating a database in PostgreSQL and would like to
> continue using copy / paste to update polygons, but in my database
> structure is different from the shape file. For example:
> Imagine that the shapefile have all the fields in one table, already in
> the database, by reason of standardization, have these columns in tables
> distinct. Below is an example of a register of towns.
> 
> File shape, columns:
> town ​​code;
> town description;
> Neighborhood code;
> name of the neighborhood;
> block code;
> Street code;
> street name;
> 
> In Postgres / Gis could look like this:
> 
> Cities table (data):
> - Town id
> - Description of town
> 
> Neighborhoods table (data):
> - Id of the neighborhood
> - Description of the neighborhood
> - Id of town (foreign key)
> 
> Blocks table:
> - Id of the court
> - Block of code
> - Town id (foreign key)
> - Geometry, polygon
> 
> Streets table:
> - Street id
> - Street name
> - Town id (foreign key)
> - Geometry, line
> 
> How could update (insert) a block in postgresql table using copy / paste
> the shape file?
> Would have to create a trigger/procedure (instead of) to automate the
> process?
> Fields of shape file should be equal to the fields of database table?
> Some practical example as a reference?
> 
> tia
> -- 
> Luciano
> 
> 
> 
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> 



signature.asc
Description: OpenPGP digital signature
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Hardware requirements for a server

[James Keener]

> You didn't talk about backup, it is essential (raid, replication, backup
> script?).

I'm sorry to be "that guy": RAID is NOT a backup. Unless you snapshot
your replication machine, replication isn't a backup either (though,
like RAID, it enables High Availability (HA)).

For lack of a precise definition, a backup should allow to recover from:

* OH CRAP! WE DELETED A FILE YESTERDAY/LAST WEEK AND NEED IT BACK!
* OH CRAP! WE CHANGED A FILE YESTERDAY/LAST WEEK AND NEED IT BACK!
* OH CRAP! THE OFFICE NO LONGER EXISTS -- WE NEED NEW SERVERS NOW!

(Also remember that that "WE" could be an upset or rouge employee.
Access control is essential!)

But to the OP, I do agree with Parent, backups are _ESSENTIAL_.

A script that runs daily and simply does pg_dump, encrypts (if
necessary/desired), and uploads to e.g. S3 each day will be invaluable
to you when you actually need it. I offer that as a very simple example.
 If your organization already has a backup process, I would suggest
trying to tie into that

Jim



signature.asc
Description: OpenPGP digital signature
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users