Re: [postgis-users] Create PostGIS point, line and polygon tables for PowerBI users

[Mateusz Loskot]

On Fri, 18 Sep 2020 at 19:56, Shaozhong SHI  wrote:
>
> Hi, Mateusz,
>
> It sounds good.  Any working example?

C'mon mate!

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-shape-map#use-custom-maps

Best regards,
-- 
Mateusz Loskot, http://mateusz.loskot.net
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Create PostGIS point, line and polygon tables for PowerBI users

[Mateusz Loskot]

On Fri, 18 Sep 2020 at 13:51, Shaozhong SHI  wrote:
>
> Any working examples for creating PostGIS point, line and polygon tables for 
> PowerBI users?
> Namely, how best to prepare map data sets in PostGIS for PowerBI users to 
> access and use.

TopoJSON

All you need is to refer to
- Microsoft documentation on using the Shape maps
- PostGIS documentation on retrieving geometry in TopoJSON

Best regards,
-- 
Mateusz Loskot, http://mateusz.loskot.net
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Max size of rasters?

[Mateusz Loskot]

On Thu, 5 Sep 2019 at 15:24, Gerry Creager - NOAA Affiliate
 wrote:
>
> I'll also go look at this thread, but would this apply to NetCDF "rasters" as 
> well?

Source image format should not matter. It is not relevant to PostGIS
storage and operations.

Best regards,
-- 
Mateusz Loskot, http://mateusz.loskot.net
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Max size of rasters?

[Mateusz Loskot]

On Thu, 5 Sep 2019 at 10:40, Tobias Gerdin 
wrote:

> Hello,
>
>
>
> We are trying to create somewhat large rasters (30-40 bands of data
> comprising a few GB of data uncompressed).
>

Check posts of the "Large GeoTIFF ingestion by PostGIS" thread in the
archive
https://lists.osgeo.org/pipermail/postgis-users/2018-May/042724.html

Best regards,
-- 
Mateusz Loskot, http://mateusz.loskot.net
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[postgis-users] ST_EstimatedExtent and identifier case sensitivity rules

[Mateusz Loskot]

Hi,

I create a table using:

CREATE TABLE "AbCdEfG" (...)

I run:

SELECT ST_EstimatedExtent('public', 'AbCdEfG', 'g')

Using PostGIS 2.1.3, this fails with "public.abcdefg relation does not exist"

Using PostGIS 2.5.2, it succeeds.

Using 2.1.3, am I just lucky to hit this regression fixed in 2.1.4?
https://trac.osgeo.org/postgis/ticket/2834

Generally, assuming no regressions occur, is this correct to
assume functions taking identifiers as literals like
ST_EstimatedExtent, AddGeometryColumn
are case-insensitive, even if tables and columns were created
as case-sensitive (double-quoted)?

Best regards,
-- 
Mateusz Loskot, http://mateusz.loskot.net
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] raster2pgsql binary in apt distros

[Mateusz Loskot]

Regina,
I don't see how raster2pgsql depends on libpq. It does depend on liblwgeom,
librtcore though.

Mateusz Loskot, mate...@loskot.net
(Sent from mobile)


On Fri, 16 Nov 2018, 08:19 Michał Garnysz  I noticed many of things you described and im i doubt there is any clean
> way to get it working. It looked like simple task get tar ball of postgres
> & postgis and make it work. (was even able to configure and make postgis
> but was installation dependant)
> I made it run instance just overloading some param -L while executing
> initdb - on extracted binaries on clean ubuntu - not 100% all was working
> fine but where able to add extension and populate data.
>
> It's sad that you can apt-get all of this in like 5steps, but there is no
> way to make it work form one binary (not installed) folder - there are
> ready to use docker container db with spatial but it's another dependency
> to project (and just for tests it's an overkill)
> Or maybe someone prove me wrong, anyone - please :)
>
> czw., 15 lis 2018 o 20:54 Regina Obe  napisał(a):
>
>> I think we can make it a bit easier on them and also easier on people who
>> just want the commandline tools
>>
>>
>>
>> 1)  Right now you can't compile the commandlines without having
>> postgres installed unless you hack the make scripts
>>
>> 2)  It has come to my attention that raster2pgsql has a dependency
>> on libpq.  I'm not sure why it should when I thought it just emits SQL
>> similar to shp2pgsql (which has no libpq dependency).
>>
>> So this sounds like a dependency that may be accidental.
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> *From:* postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] *On
>> Behalf Of *Paul Ramsey
>> *Sent:* Thursday, November 15, 2018 2:39 PM
>> *To:* PostGIS Users Discussion 
>> *Subject:* Re: [postgis-users] raster2pgsql binary in apt distros
>>
>>
>>
>> Something to think about for our 3.0 release.
>>
>>
>>
>> Might need to be ticketed and assigned to packagers? It's not a big ask,
>> but it's easy to forget if they just copy'n'ship the old specs.
>>
>>
>> P
>>
>>
>>
>>
>>
>>
>> > -Original Message-
>> > From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On
>> Behalf
>> > Of Nils Nolde
>> > Sent: Thursday, November 15, 2018 5:55 AM
>> > To: postgis-users@lists.osgeo.org
>> > Subject: [postgis-users] raster2pgsql binary in apt distros
>> >
>> > Hi,
>> >
>> > I had trouble accessing the raster2pgsql binary with different apt
>> postgresql-x-
>> > postgis-x distros in the past. It�s not callable as executable and I
>> can�t find it
>> > in the usual bin/ directories. I can only access it when installing �
>> apt-get
>> > install postgis� which leaves me with a postgresql v10 distro. Not a
>> > dealbreaker, but a bit annoying. So my questions are:
>> >
>> > - how can I access raster2pgsql in a postgres specific postgis version
>> from apt,
>> > e.g. postgresql-9.6-postgis-2.4?
>> >
>> > - is there a way to compile the tool independent of a postgis
>> installation, as
>> > to not have a full postgres installation for just using raster2pgsql?
>> Context:
>> > sometimes I only need the tool in a docker container to update an
>> external
>> > database.
>> >
>> > Or does it need compilation against specific postgis versions? I guess
>> GDAL is a
>> > dependency, but the produced .sql should be rather independent of
>> postgis
>> > versions right?
>> >
>> > Of course I could write my own sql statements for raster import, but it
>> �s sooo
>> > convenient:)
>> >
>> > Many thanks
>> > Nils
>> > ___
>> > 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
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] What's the Fastest way to find if Polygons cover an input point?

[Mateusz Loskot]

On 3 July 2018 at 12:50, Devdatta Tengshe  wrote:
>
> Currently I'm using the following query to figure out if we can potentially
> service the customer:
>
> Select count(id) from service_areas where
> ST_CoveredBy(ST_SetSRID(ST_MakePoint(78.46239,17.30360),4326), geom);

Try https://postgis.net/docs/ST_Within.html

Best regards,
-- 
Mateusz Loskot, http://mateusz.loskot.net
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Bounding Box of Spatial Index for Points

[Mateusz Loskot]

On 1 March 2018 at 14:30, Frederick Powers <orangehan...@gmail.com> wrote:
>
> How is the bounding box determined when creating a spatial index for point
> geometries?   It's my understanding that creating a spatial index utilizes
> bounding boxes (...)

Generally there are options to build clusters, eg.
https://beta.observablehq.com/@mourner/hilbert-curve-packing

Best regards,
-- 
Mateusz Loskot, http://mateusz.loskot.net
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Raster Table Name With Upper-Case Characters

[Mateusz Loskot]

On 14 July 2017 at 14:27, Osahon Oduware <osahon@gmail.com> wrote:
> Hi Mateusz,
>
> I have just tried using the -q option and it works. Thanks a lot. I have
> gone through the doc a lot to resolve several issues in the past. In this
> case it was an oversight as I thought that explicitly wrapping the
> identifiers in quote would give the same result as the -q option.

raster2pgsql is modelled after shp2pgsql, in terms of capabilities,
so you shouldn't need any quoting/escaping gymnastics to tell the loaders
what case of names you want.

It might have been easier if raster2pgsql used -k as shp2pgsql does,
but -k is taken by different option.

raster2pgsql docs on -q could have been clearer though.
Patches are welcome.

Best regards,
-- 
Mateusz Loskot, http://mateusz.loskot.net
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Raster Table Name With Upper-Case Characters

[Mateusz Loskot]

On 14 July 2017 at 12:44, Osahon Oduware <osahon@gmail.com> wrote:
>
> Thanks for your suggestion. I would attempt this on a small raster and give
> you feedback. However, in practice I am working with a large raster, hence,
> I am piping the output of the raster2pgsql command with the connection to
> the database as below:
> raster2pgsql -s  -d -Y -e -I -C -F -M /path/to/raster/file
>  | psql -h localhost -U postgres -p 5432 -d 
>

If you scanned the docs you would have learned about -q option.

(reading PostgreSQL docs on quoted identifiers might be useful too).

If quoted identifiers are not requested, raster2pgsql forces database
object names (schema/table...) to lowercase.

Best regards,
-- 
Mateusz Loskot, http://mateusz.loskot.net
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Build PostgreSQL With XML Support on Linux

[Mateusz Loskot]

On 31 May 2017 at 15:03, Osahon Oduware <osahon@gmail.com> wrote:
> Noted. Thanks. I don't seem to get much help from pgsql-general group.

May be try on IRC channel(s)
https://www.postgresql.org/community/irc/


Best regards,
-- 
Mateusz Loskot, http://mateusz.loskot.net
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Build PostgreSQL With XML Support on Linux

[Mateusz Loskot]

On 31 May 2017 at 14:18, Osahon Oduware <osahon@gmail.com> wrote:
> Hi All,
>
> I successfully built PostgreSQL from source on Linux (Centos 7) using the
> following:
> ./configure --prefix=/home/nagispg/local/pgsql --with-libxml --with-libxslt
> [...]
> Could someone point me to what I am doing wrong.

Asking wrong list, for the starter.
Your question belongs to https://www.postgresql.org/list/pgsql-general/

And, forgetting netiquette and copying the same post to postgis-devel
which topic [1] says:
"is devoted to discussion of ongoing development to the PostGIS"

[1] https://lists.osgeo.org/mailman/listinfo/postgis-devel

Best regards,
-- 
Mateusz Loskot, http://mateusz.loskot.net
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Raster2pgsql - preserving number of decimal places

[Mateusz Loskot]

On 24 Sep 2016 22:56, "Nick Ward"  wrote:
>
> SELECT filename, rid, ST_Value(rast, pt.geom) As val

round(ST_Value(...), 2)

-- 
Mateusz Łoskot
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Postgis raster : jfif? jp2?

[Mateusz Loskot]

On 25 November 2013 14:22, laurent lcel...@latitude-geosystems.com wrote:
 When i type: raster2pgsql -G , the jpeg jfif seems to be in the list. Why i
 have this error message? Except tiff, what raster format can be imported
 into postgis db?

Read the docs, Luke!

loads GDAL supported raster formats [1]

and

-G

Print the supported raster formats. [1]

[1] 
http://postgis.net/docs/manual-2.1/using_raster_dataman.html#RT_Loading_Rasters

Best regards,
-- 
Mateusz  Loskot, http://mateusz.loskot.net
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] The first release of the PostGIS Add-ons is out!

[Mateusz Loskot]

 I would have gone with a license saying You can modify and redistribute as 
 long as the derived work is also under an open license, not necessarily GPL. 
 Does that make sense? Does that exist?

http://en.wikipedia.org/wiki/WTFPL

Best regards,
-- 
Mateusz  Loskot, http://mateusz.loskot.net
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] st_band with out-db raster data

[Mateusz Loskot]

On 1 November 2013 15:10, Dilley, Andrew-P57500 andrew.dil...@gdc4s.com wrote:
 From that thread,

 We need to do away with the entire in-db and out-db notion and just return 
 the pixel values.

 I guess this is what I mean.  Should st_band be doing this?

I understand this is not easy to follow the thread and the comments
posted to the #2217 ticket. However, the conclusion you can draw from
those discussions is that the raster WKB format as well as
the default behaviour of basic API does not change -
PostGIS raster keeps to return server path

http://trac.osgeo.org/postgis/ticket/2217#comment:23

The WKB format is currently defined (version 0) is unable to properly
handle out-db raster bands for clients.


Hence, there is related ticket that adds extra parameter to override
default behaviour of the APIs

http://trac.osgeo.org/postgis/ticket/

I'm not a heavy user of out-db rasters, but I *perhaps* there are other APIs
like ST_Band, that should be updated according as ST_Binary has
been changed as result of the # ticket.

Anyway, I'm just trying to explain you the current situation, but
I'll leave the decisions about API changes to Pierre and Bborie.

 (I'm admittedly very new to postgis so maybe this a stupid question).

IMO, no question is stupid.

Best regards,
-- 
Mateusz  Loskot, http://mateusz.loskot.net
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] st_band with out-db raster data

[Mateusz Loskot]

On 1 November 2013 16:38, dustymugs dustym...@gmail.com wrote:
 I don't believe ST_Band or most functions will have the outasin flag added. 
 ST_AsBinary is for exporting rasters from PostGIS unlike ST_Band which 
 creates a new raster within PostGIS.

 If anything, a function that converts an out-db band to in-db band may be 
 useful.


Sure, good point Bborie.

Best regards,
-- 
Mateusz  Loskot, http://mateusz.loskot.net
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users