Re: [GeoAlchemy] Geoalchemy2 and Geography

[Eric Lemoine]

On Thu, Dec 13, 2012 at 7:23 AM, Zwieberl <zwie...@lavabit.com> wrote:
> Hello,
>
> I am working with geoalchemy2 and PostGIS2 at the moment, and it works quite
> nicely.
> But now I stumbled upon a problem for which I have not found a solution in
> days.
>
> My database contains two tables (lets call them A and B) which each contain
> one Geography-Column with type POINT.
> My goal is to walk through table A point by point and search for all points
> in B in the vicinity of current point A.
> Using ST_DWithin works like a charm, but I need to do the same with a
> rectangle (Note: Not a square!).
> Now I planned to create a POLYGON on the fly and use ST_Intersects or
> ST_Coveredby/ST_Covers.
>
> My problem is now the creation of the POLYGON-Rectangle, since it has to be
> located with respect to the current A.POINT
>
> I tried something like (with more points of course to create a rectangle.
> This is just a shortened version):
>
> query = db.query(source, target)
> polygon = "POLYGON(" + source.geo.ST_X() + " " + source.geo.ST_Y() + "," +
> source.geo.ST_X()-dX + " " +
> source.geo.ST_Y()-dY + "," +
> source.geo.ST_X() + " " +
> source.geo.ST_Y()) + ")"
>
> result = query(source, target).filter(target.geo.intersects((polygon)))
>
> but this does not work, since ST_X is not available for Geography-Type. And
> I don't know how to make the ST_X(geo::geometry) - cast within Geoalchemy2.
>
> So I tried to make this one long string within PostGIS using the
> concat-method:
>
> polygon = sqlalchemy.func.concat("'POLYGON(('," +
> "(ST_X(" + source.__tablename__ + ".geo::geometry)+(" + str(-dX) +
> ")),' '," +
> "(ST_Y(" + source.__tablename__ + ".geo::geometry)+(" + str(-dY) +
> ")),','," +
> ..... etc.
>
> But now the string will created within the SELECT-statement, so
> ST_Intersect(geography, text) will be called which produces of course an
> error.
> To make this work, I would need to call ST_GeogFromText with this string,
> but I could not figure out how to call this function explicitly from within
> Geoalchemy2.
>
> A third possibility would be using ST_Project to move the current Point in 4
> different directions. But how could I create a Polygon out of 4
> Point-entities?
>
> I could of course just launch the whole select-statement as a string, but
> since i have many other filters (which are dynamically added or not added)
> this wouldn't work very easily.
>
> Do you have any ideas how to solve this problem? (Maybe even with a
> completely different approach)
>
> Any help would be welcomed!
>
> Thank you!



FWIW the following works in PostGIS 2:

geoalchemy2=# SELECT ST_Expand(CAST(ST_GeogFromText('POINT(0 0)') AS
geometry(POINT, 4326)), 1);

and the following works with GeoAlchemy2:

func.ST_Expand(cast(func.ST_GeogFromText('POINT(0 0)'),
types.Geometry(geometry_type='POINT', srid=4326)), 1)


Hope it helps,


--
Eric

[Eric Lemoine]

On Fri, Dec 14, 2012 at 9:33 AM, Zwieberl <zwie...@lavabit.com> wrote:
>
>> FWIW the following works in PostGIS 2:
>>
>> geoalchemy2=# SELECT ST_Expand(CAST(ST_GeogFromText('POINT(0 0)') AS
>> geometry(POINT, 4326)), 1);
>>
>> and the following works with GeoAlchemy2:
>>
>> func.ST_Expand(cast(func.ST_GeogFromText('POINT(0 0)'),
>> types.Geometry(geometry_type='POINT', srid=4326)), 1)
>>
>>
>> Hope it helps,
>
>

> Thanks, but the problem is, that ST_Expand creates a square, but I need a
> rectangle.
>
> But I solved the problem (not very pretty, but a solution nonetheless), by
> implementing ST_GeogFromText on my own.
> See this for details:
> https://github.com/geoalchemy/geoalchemy2/issues/7#issuecomment-11367337


Regarding your comment:

> Note: It is quite annoying that ST_X does not work for Geographies, therefore I have to use ST_AsText + trim from sqlalchemy.

Note that the following works:

SELECT ST_X(CAST(ST_GeogFromText('POINT(5 54)') AS geometry(POINT, 4326)));

I'm not sure it would help you though.

--
Eric