Error on geometry summary

sirex commented 1 year ago

http https://get.data.gov.lt/datasets/gov/lsd/covid19/LigoniniuDuomenys/:summary/koord

HTTP/1.1 500 Internal Server Error

{
    "errors": [
        {
            "code": "InternalError",
            "message": '
                (psycopg2.errors.InternalError_) Operation on mixed SRID geometries
                SQL: 
                    WITH clusters AS (
                        SELECT 
                            ST_ClusterKMeans(model."koord", 100) OVER() AS cluster_id,
                            model."koord" AS geom,
                            model._id AS _id,
                            model._created as created_at
                        FROM "datasets/gov/lsd/covid19/LigoniniuDuomenys" AS model
                    )
                    SELECT 
                        ST_NumGeometries(ST_Collect(geom)) as cluster,
                        ST_AsText(ST_Centroid(ST_Collect(geom))) AS centroid,
                        (ARRAY_AGG(clusters._id))[1] AS _id
                    FROM clusters
                    GROUP BY cluster_id
                    ORDER BY MIN(clusters.created_at);
            '
        }
    ]
}

Logs:

[ERROR] Exception in ASGI application
Traceback (most recent call last):
  File "sqlalchemy/engine/base.py", line 1705, in _execute_context
    self.dialect.do_execute(
  File "sqlalchemy/engine/default.py", line 716, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.InternalError_: Operation on mixed SRID geometries
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
  File "spinta/api.py", line 95, in homepage
    return await create_http_response(context, params, request)
  File "spinta/utils/response.py", line 139, in create_http_response
    return await commands.summary(
  File "spinta/commands/read.py", line 455, in summary
    return render(context, request, model, params, rows, action=action)
  File "spinta/renderer.py", line 23, in render
    return commands.render(
  File "spinta/formats/html/commands.py", line 139, in render
    return _render_model(context, request, model, action, params, data, headers)
  File "spinta/formats/html/commands.py", line 243, in _render_model
    ctx = build_template_context(
  File "spinta/formats/html/commands.py", line 217, in build_template_context
    rows, empty = _is_empty(rows)
  File "spinta/formats/html/commands.py", line 171, in _is_empty
    row = next(rows)
  File "spinta/utils/nestedstruct.py", line 15, in flatten
    for v in vals:
  File "spinta/accesslog/__init__.py", line 162, in log_response
    for row in rows:
  File "spinta/backends/postgresql/commands/summary.py", line 446, in summary
    result = connection.execute(f'''
  File "sqlalchemy/engine/base.py", line 1185, in execute
    return self._exec_driver_sql(
  File "sqlalchemy/engine/default.py", line 716, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.InternalError: (psycopg2.errors.InternalError_) Operation on mixed SRID geometries
SQL:
  WITH clusters AS (
      SELECT
      ST_ClusterKMeans(
          model."koord",
          100
      ) OVER() AS cluster_id,
      model."koord" AS geom,
      model._id AS _id,
      model._created as created_at
      FROM "datasets/gov/lsd/covid19/LigoniniuDuomenys" AS model

  )
  SELECT
      ST_NumGeometries(ST_Collect(geom)) as cluster,
      ST_AsText(ST_Centroid(ST_Collect(geom))) AS centroid,
      (ARRAY_AGG(clusters._id))[1] AS _id
  FROM clusters
  GROUP BY cluster_id
  ORDER BY MIN(clusters.created_at);

Susiję

sirex commented 1 year ago

It looks, that issue is related to incorrect types in database. After running:

ALTER TABLE "datasets/gov/lsd/covid19/LigoniniuDuomenys"
    ALTER COLUMN "koord"
        TYPE geometry(Geometry, 4326)
        USING ST_SetSRID("koord", 4326);

the error disappeared.

JuliusLADP commented 9 months ago

Pereiti per rinkinius, kuriuose naudojamas geom tipas ir patikrinta ar nemeta klaidos. Buvo siūstas Aurimui sąrašas

atviriduomenys / spinta

Error on geometry summary #520

Susiję