Re: [GeoAlchemy] SRID not making it into Postgres, says 'violates check constraint "enforce_srid_location"'

[Eric Lemoine]

On Thu, Jun 14, 2012 at 5:56 AM, Tom Dalling <tom.d...@gmail.com> wrote:
> Hi,
>
> I'm having trouble with the SRID when inserting points into
> Postgres/PostGIS. I'm getting this error from Postgres:
>
> ERROR:  new row for relation "occurrences" violates check constraint
> "enforce_srid_location"
>
>
> This is the SQL causing the error, straight from the Postgres logs:
>
> INSERT INTO occurrences (location, rating, species_id, source_id,
> source_rating)
> VALUES (E'POINT (2.0000000000000000 1.0000000000000000)', E'invalid', 209,
> 100, E'invalid')
> RETURNING occurrences.id
>
>
> This is the Python code that inserts the row:
>
>
> p = shapely.geometry.Point(occ.coord.longi, occ.coord.lati)
> location = WKTSpatialElement(shapely.wkt.dumps(p), 4326)
>
> db.occurrences.insert().\
>
> returning(db.occurrences.c.id).\
>
> execute(
>
> location=location,
>
> source_rating=rating,
>
> rating=rating,
>
> species_id=species_id,
>
> source_id=self.source_row_id
>
> ).scalar()
>
>
> I tested out some inserts in psql, and it seems the error is triggered if
> the SRID isn't set on the point. For instance this will trigger the error:
>
> ST_GeomFromText('POINT(-71.064544 42.28787)')
>
>
> But this works just fine:
>
> ST_GeomFromText('POINT(-71.064544 42.28787)', 4326)
>
>
> My guess is that the SRID isn't making it into the SQL, but I can't work out
> why. Any ideas?



I'd like to reproduce that. It would be ideal if you could come up
with a self-contained test-case, like this:
<https://github.com/geoalchemy/geoalchemy/issues/17#issuecomment-6069375>.
I could do it myself, but I won't be able to do it until a few weeks.


Thanks,

--
Eric

[Pierre GIRAUD]

I have a similar problem when a SRID is set for the geometry column.

Here's a test case: http://pastie.org/8423515

Any hint for a workaround would be appreciated.

For example, is there a way to tell SQLAlchemy to use ST_GeomFromWKB instead of ST_GeomFromText?

Thanks,

Pierre

[Eric Lemoine]

Le mercredi 23 octobre 2013, Pierre GIRAUD a écrit :

I have a similar problem when a SRID is set for the geometry column.

Here's a test case: http://pastie.org/8423515

Use WKTElement and pass an srid instead.

https://geoalchemy-2.readthedocs.org/en/latest/elements.html

Hope it works.

--
Eric

[Pierre GIRAUD]

OK, it works.
However, it doesn't when I want to do bulk insert.

conn.execute(points.insert(), [{'geom':
geoalchemy2.elements.WKTElement('POINT(0 0)', 4326)}, {'geom':
geoalchemy2.elements.WKTElement('POINT(10 0)', 4326)}])

I get the following error: can't adapt type 'WKTElement'

http://pastie.org/8424182

It has already been reported by Oliver Tonnhofer a while ago. ;-)
https://groups.google.com/forum/#!searchin/geoalchemy/bulk/geoalchemy/Y7gzABaZOUw/9nin3my5xDEJ

Any hint?

> --
> You received this message because you are subscribed to a topic in the
> Google Groups "GeoAlchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/geoalchemy/HHu3P0zc1_A/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> geoalchemy+...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.



--
-------------------------------------------------------------
| Pierre GIRAUD
-------------------------------------------------------------

[Eric Lemoine]

On Wed, Oct 23, 2013 at 4:20 PM, Pierre GIRAUD <pierre...@gmail.com> wrote:
> OK, it works.
> However, it doesn't when I want to do bulk insert.
>
> conn.execute(points.insert(), [{'geom':
> geoalchemy2.elements.WKTElement('POINT(0 0)', 4326)}, {'geom':
> geoalchemy2.elements.WKTElement('POINT(10 0)', 4326)}])
>
> I get the following error: can't adapt type 'WKTElement'
>
> http://pastie.org/8424182
>
> It has already been reported by Oliver Tonnhofer a while ago. ;-)
> https://groups.google.com/forum/#!searchin/geoalchemy/bulk/geoalchemy/Y7gzABaZOUw/9nin3my5xDEJ
>
> Any hint?

I don't know how to fix this at this point. I'd need to take the
question to the sqlalchemy list.


--
Eric

[Eric Lemoine]

Curious to know if the following works: 

 conn.execute(points.insert(), [{'geom': func.ST_GeomFromText('POINT(0 0)', 4326)}, {'geom': func.ST_GeomFromText('POINT(10 0)', 4326)}])

--
Eric

[Pierre GIRAUD]

No it doesn't. GeomFromText is already called. I get the following error:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) can't adapt type
'Function' 'INSERT INTO points (geom) VALUES
(ST_GeomFromText(%(geom)s))' ({'geom':
<sqlalchemy.sql.expression.Function at 0xb661962c; ST_GeomFromText>},
{'geom': <sqlalchemy.sql.expression.Function at 0xb661972c;
ST_GeomFromText>})

For your information, the main difference between the single and bulk
insert as done in the pastie code is that the WKTElement is split into
WKT + SRID in the first case but not in the second one.

## single insert
INSERT INTO points (geom) VALUES
(ST_GeomFromText(%(ST_GeomFromText_1)s, %(ST_GeomFromText_2)s))
RETURNING points.id
{'ST_GeomFromText_1': 'POINT(0 0)', 'ST_GeomFromText_2': 4326}

## bulk insert
INSERT INTO points (geom) VALUES (ST_GeomFromText(%(geom)s))
({'geom': <WKTElement at 0xb66fb8ac; 'POINT(0 0)'>}, {'geom':
<WKTElement at 0xb66fd6ac; 'POINT(10 0)'>})

Full stack trace: http://pastie.org/8426235

[Pierre GIRAUD]

Here's a new code to show different cases where it does or doesn't work.
http://pastie.org/8429786

I tried to figure out what was happening behind but it was a bit
difficult for me.
Where is the code who decides to use GeomFromText when inserting new
records? It seems to be the case even when a WKBElement is set in the
values.

Any help is appreciated.

[Eric Lemoine]

On Fri, Oct 25, 2013 at 4:04 PM, Pierre GIRAUD <pierre...@gmail.com> wrote:
> Here's a new code to show different cases where it does or doesn't work.
> http://pastie.org/8429786
>
> I tried to figure out what was happening behind but it was a bit
> difficult for me.
> Where is the code who decides to use GeomFromText when inserting new
> records? It seems to be the case even when a WKBElement is set in the
> values.
>
> Any help is appreciated.

Thanks for the test cases.

Some more details on the issue:

When doing
points.insert().values(geom=geoalchemy2.elements.WKTElement('POINT(0 0)', 4326))
there's no param binding, and it's the WKTElement object that "wraps
itself" in an ST_GeomFromText call. See
<https://github.com/geoalchemy/geoalchemy2/blob/master/geoalchemy2/elements.py#L65-66>

When doing
insert = points.insert()
conn.execute(insert, {'geom':
geoalchemy2.elements.WKTElement('POINT(0, 0)', 4326)})
the "geom" param is bound at statement execution time. In that case,
the Geometry type's bind_expression mechanism triggers. See
<https://github.com/geoalchemy/geoalchemy2/blob/master/geoalchemy2/types.py#L109-110>

There definitely are problems in the latter case (binding).

First, we don't have any code for converting the WKTElement object to
a string we can wrap in an ST_GeomFromText call and send to the DB.
This can be fixed by adding a bind_processor function to the Geometry
(and Geography) type. See
<http://docs.sqlalchemy.org/en/rel_0_8/core/types.html?highlight=bind_expression#sqlalchemy.types.TypeEngine.bind_processor>.

Also, bind_expression does not currently pass any srid value to
ST_GeomFromText. And, at this point, I'm actually not sure what should
be done and how. What would make sense to me is to have
bind_expression get the srid from the bindvalue, but it looks like
that's not possible, as AIUI, bind_expression is called only once in
the case of in an executemany() call.

I have no good solution for now.

--
Eric

[Eric Lemoine]

One option may be to use ST_GeomFromEWKT instead of ST_GeomFromText in
bind_expression. Pierre, can you test if
<https://github.com/elemoine/geoalchemy2/compare/ewkt> works for you?

--
Eric

[Pierre GIRAUD]

Works great.
Thanks so much.