Ash Framework•3mo ago

indeterminate_datatype with fragment using the PosGIS

i need determine if a record its inside a polygon that its a param from the call, but returns ERROR 42P18 (indeterminate_datatype) could not determine data type of parameter $1 and dont really know why its happening, i tried on direct sql query and works well with the format and returns the polygon, so dont know why fails with on the format i think that maybe its injecting like POLYGON(("latitude longitude,latitude longitude") and maybe quotes its making some conflict but dont really know, same query with same data but in direct sql its working

  calculate :inside_polygon,
              :boolean,
              expr(
                fragment(
                  "ST_Intersects(ST_GeomFromText('POLYGON((?))', 4326), ?)",
                  ^arg(:polygon),
                  coordinates
                )
              ) do
      argument :polygon, :string, allow_nil?: false
      public? true
    end

  calculate :inside_polygon,
              :boolean,
              expr(
                fragment(
                  "ST_Intersects(ST_GeomFromText('POLYGON((?))', 4326), ?)",
                  ^arg(:polygon),
                  coordinates
                )
              ) do
      argument :polygon, :string, allow_nil?: false
      public? true
    end

coordinates its a value of type :geometry polygon argument its a string but in format latitude longitude,latitude longitude,... the error generates this query of the calculation

15 Replies

ZachDaniel•3mo ago

try adding types to the fragment call

 calculate :inside_polygon,
              :boolean,
              expr(
                fragment(
                  "ST_Intersects(ST_GeomFromText('POLYGON((?))', 4326), ?)",
                  type(^arg(:polygon), :string),
                  type(coordinates, :whatever_type_this_is)
                )
              ) do
      argument :polygon, :string, allow_nil?: false
      public? true
    end

 calculate :inside_polygon,
              :boolean,
              expr(
                fragment(
                  "ST_Intersects(ST_GeomFromText('POLYGON((?))', 4326), ?)",
                  type(^arg(:polygon), :string),
                  type(coordinates, :whatever_type_this_is)
                )
              ) do
      argument :polygon, :string, allow_nil?: false
      public? true
    end

POLYGON(("latitude longitude,latitude longitude") it does not inject it that way wait ah 'POLYGON((?))' wouldn't work I don't think. You're creating a string.

 calculate :inside_polygon,
              :boolean,
              expr(
                fragment(
                  "ST_Intersects(ST_GeomFromText(?, 4326), ?)",
                  "'POLYGON(#{^arg(:polygon)})'", 
                  coordinates
                )
              ) do
      argument :polygon, :string, allow_nil?: false
      public? true
    end

 calculate :inside_polygon,
              :boolean,
              expr(
                fragment(
                  "ST_Intersects(ST_GeomFromText(?, 4326), ?)",
                  "'POLYGON(#{^arg(:polygon)})'", 
                  coordinates
                )
              ) do
      argument :polygon, :string, allow_nil?: false
      public? true
    end

you'd want to do something like that, to create a string value the way that you want although I think your calculation would make more sense if it took a lat/lng instead of a string of coordinates

nicolkillOP•3mo ago

i tried in you way but says that %Ash.Error.Query.NoSuchFunction{function: :arg} if you can tell me the right format to use a geometry as argument would be great!

ZachDaniel•3mo ago

I'm not that familiar with working with geometry TBH can I see your latest attempt please? That gave you that error?

nicolkillOP•3mo ago

on using this example, gives the NoSuchFunction error

ZachDaniel•3mo ago

Can you please copy your version here so I can be sure?

nicolkillOP•3mo ago

    calculate :inside_polygon,
              :boolean,
              expr(
                fragment(
                  "ST_Intersects(ST_GeomFromText(?, 4326), ?)",
                  "'POLYGON((#{^arg(:polygon)}))'",
                  coordinates
                )
              ) do
      argument :polygon, :string, allow_nil?: false
      public? true
    end

    calculate :inside_polygon,
              :boolean,
              expr(
                fragment(
                  "ST_Intersects(ST_GeomFromText(?, 4326), ?)",
                  "'POLYGON((#{^arg(:polygon)}))'",
                  coordinates
                )
              ) do
      argument :polygon, :string, allow_nil?: false
      public? true
    end

ZachDaniel•3mo ago

That is very strange Try this please

nicolkillOP•3mo ago

complete exception

     ** (MatchError) no match of right hand side value: {:error, %Ash.Error.Query.NoSuchFunction{function: :arg, arity: nil, resource: Miraj.Loci.Locus, splode: nil, bread_crumbs: [], vars: [], path: [], stacktrace: #Splode.Stacktrace<>, class: :invalid}}

     ** (MatchError) no match of right hand side value: {:error, %Ash.Error.Query.NoSuchFunction{function: :arg, arity: nil, resource: Miraj.Loci.Locus, splode: nil, bread_crumbs: [], vars: [], path: [], stacktrace: #Splode.Stacktrace<>, class: :invalid}}

ZachDaniel•3mo ago

    calculate :inside_polygon,
              :boolean,
              expr(
                fragment(
                  "ST_Intersects(ST_GeomFromText(?, 4326), ?)",
                  "'POLYGON(" <> ^arg(:polygon) <> ")'",
                  coordinates
                )
              ) do
      argument :polygon, :string, allow_nil?: false
      public? true
    end

    calculate :inside_polygon,
              :boolean,
              expr(
                fragment(
                  "ST_Intersects(ST_GeomFromText(?, 4326), ?)",
                  "'POLYGON(" <> ^arg(:polygon) <> ")'",
                  coordinates
                )
              ) do
      argument :polygon, :string, allow_nil?: false
      public? true
    end

try that

nicolkillOP•3mo ago

said invalid geometry because on the POLYGON call needs double (()) im trying again nope, the same, at least its other error 🤣

ZachDaniel•3mo ago

Still getting indeterminate types? Can you share the full error? Also would be good if you could show the generated query in the logs/error somewhere

nicolkillOP•3mo ago

im on the unit tests, how can i get the query? its from a json_api call my test

ZachDaniel•3mo ago

Logger.configure(level: :debug) in hte test and run just that one test

nicolkillOP•3mo ago

                fragment(
                  "ST_Intersects(ST_GeomFromText('POLYGON((?))', 4326), ?)",
                  ^arg(:polygon),
                  coordinates
                )

                fragment(
                  "ST_Intersects(ST_GeomFromText('POLYGON((?))', 4326), ?)",
                  ^arg(:polygon),
                  coordinates
                )

this fragments returns indeterminate_datatype

                fragment(
                  "ST_Intersects(ST_GeomFromText(?, 4326), ?)",
                  "'POLYGON((" <> ^arg(:polygon) <> "))'",
                  coordinates
                )

                fragment(
                  "ST_Intersects(ST_GeomFromText(?, 4326), ?)",
                  "'POLYGON((" <> ^arg(:polygon) <> "))'",
                  coordinates
                )

this returns (Postgrex.Error) ERROR XX000 (internal_error) parse error - invalid geometry i think that i found a solution

                fragment(
                  "ST_Intersects(ST_GeomFromText(?, 4326), ?)",
                  "POLYGON((" <> ^arg(:polygon) <> "))",
                  coordinates
                )

                fragment(
                  "ST_Intersects(ST_GeomFromText(?, 4326), ?)",
                  "POLYGON((" <> ^arg(:polygon) <> "))",
                  coordinates
                )

this works removing the ' , technically the quote its for show that its an string, but injecting as var technically its a string so no needed works now, but fails the query 🤣 but its not for compile so i think that this its solved

  calculate :inside_polygon,
              :boolean,
              expr(
                fragment(
                  "ST_Intersects(ST_GeomFromText(?, 4326), ?)",
                  "POLYGON((" <> ^arg(:polygon) <> "))",
                  coordinates
                )
              ) do
      argument :polygon, :string, allow_nil?: false
      public? true
    end

  calculate :inside_polygon,
              :boolean,
              expr(
                fragment(
                  "ST_Intersects(ST_GeomFromText(?, 4326), ?)",
                  "POLYGON((" <> ^arg(:polygon) <> "))",
                  coordinates
                )
              ) do
      argument :polygon, :string, allow_nil?: false
      public? true
    end

final calculation to found a point inside a polygon using PosGIS

ZachDaniel•3mo ago

🤔 I'm surprsied the double quotes works TBH just double check the query to make sure it looks good 😄

Gaming

Programming

indeterminate_datatype with fragment using the PosGIS

Did you find this page helpful?