Jump to content

Do and write a table with results from multiples queries to kml file


speedftw

Recommended Posts

I'm trying to use php and postgresql to generate a kml file (google earth) that have information of multiples places.

So I have multiples queries to retrieve all the information that I need.

Example:

 

SELECT c.latitude, c.longitude

FROM Circuit AS b, Instance AS c, InstanceNumber AS d

WHERE c.id = d.Instance

AND d.Circuit = b.id

AND b.code = 'Z3D2'

 

SELECT c.number, b.model, e.pollution

FROM Asset AS a, Model AS b, TowerNumber AS c, Line AS d, Instance AS e

WHERE COALESCE(a.Model,0)=b.id

AND c.Asset=a.id

AND a.Line=d.id

AND e.Asset=a.id

AND e.Source=1

AND d.name='Z3D2'

 

 

The field "number" in both TowerNumber and InstanceNumber should be similar.

 

I can't get all the information with just one query because some field are null for Instance.source and others are not.

Besides that the foreign Keys that connect tables are not filled in all tables so I have to do different "ways" to get the information.

1st: Line->Asset->TowerNumber->Instance

2nd: Line->Circuit->InstanceNumber->Instance

For Source='SD'which as 'latitude' and 'longitude' I have two use the 1st path.

For the other information that have Source="HD" I've to use the 2nd path.

 

 

I have these two queries as an example.

What I want to do is get the information in this two queries and create and array like:

 

number | model | pollution | latitude | longitude

    0      |  ZD1  |      7      |  48,48  |  2,22

    1      |  H3    |      6      |  48,49  |  2,24

    ...      |  ...    |      ...      |      ...    |    ...

 

 

And then I'll just write that information to the kml file where each place should have all the information and should look like:

 

<Placemark>

<name>$table[number]</name>

<ExtendedData>

<Data name=name_field_1>

<value>$table[model]</value>

</Data>

<Data name=name_field_2>

<value>$table[pollution]</value>

</Data>

...

...

<Point><coordinates>$table[longitude],$table[latitude]</coordinates></Point>

</Placemark>

Link to comment
Share on other sites

 

What I want to do is get the information in this two queries and create and array like:

 

number | model | pollution | latitude | longitude

    0      |  ZD1  |      7      |  48,48  |  2,22

    1      |  H3    |      6      |  48,49  |  2,24

    ...      |  ...    |      ...      |      ...    |    ...

 

 

 

I don't know how to make a 2d array from the results of the queries.

Link to comment
Share on other sites

From what I'm seeing there is no need for two queries, as you're trying to print out data which has a 1-1 relation. The data you're getting in your first query is even in one of the tables you're fetching in the second one, making it completely superfluous. Just add those to fields to the second query, and you should be set.

 

PS: You will also want to rewrite your query, to specify the JOINs properly. Using intrinsic joins like this can seem easier to start with, but they're a lot harder to maintain. Also, it's quite easy to make a Cartesian product with them, which would increase the data retrieved exponentially.

Link to comment
Share on other sites

I can't get all the information with just one query because some field are null for Instance.source and others are not.

Besides that the foreign Keys that connect tables are not filled in all tables so I have to do different "ways" to get the information.

1st: Line->Asset->TowerNumber->Instance

2nd: Line->Circuit->InstanceNumber->Instance

For Source='SD'which as 'latitude' and 'longitude' I have two use the 1st path.

For the other information that have Source="HD" I've to use the 2nd path.

 

 

As I try to explain because the fields that I need for each point are not acessible from a single query.

The Instance table has:

- id

- id_asset

- id_model

- source

- latitude

- longitude

- pollution

 

Example of instance:

id | id_asset | id_model | source | latitude | longitude| pollution

1        100        NULL          SD        42,4        2,2          NULL

2        100          3              HD        NULL      NULL        Medium

3          22          12            HD        NULL      NULL        High

 

So, for the same asset (place) that I want to put in the kml file I have the information in the same table but in different rows.

If I do as you said I get no results from the query.

Besides, I have more queries that I want to do not just these two.

Link to comment
Share on other sites

I can't get all the information with just one query because some field are null for Instance.source and others are not.

That's what you have LEFT/RIGHT joins for.

 

Besides that the foreign Keys that connect tables are not filled in all tables so I have to do different "ways" to get the information.

This, I think, is the original problem. If you have two (or more) tables that are connected, the Foreign Key(s) should always be filled. If it is not, then you're stating that the rows are not associated with each other. You'd find that the solution to your problem would be quite easy, as long as you first fixed the missing foreign keys before putting a constraint on any future foreign keys.

 

If that is not possible (and I mean impossible, not that you just don't want to do it), then you'll need to pull the data out from each table individually. Then use a function to loop through the results, and return the finished product per table. Concatenate it to the results from the previous tables, and you're set.

Link to comment
Share on other sites

 

If that is not possible (and I mean impossible, not that you just don't want to do it), then you'll need to pull the data out from each table individually. Then use a function to loop through the results, and return the finished product per table. Concatenate it to the results from the previous tables, and you're set.

 

That's exactly what I need.

But I don't know how to concatenate all the results from the queries.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.