Sounds like you're on track Martha! I'm glad we could help. -Alexei Director of Web Development - Farallon Geographics, Inc. - 971.227.3173
On Fri, Oct 4, 2019 at 9:56 PM Martha S <[email protected]> wrote: > That worked, Adam, > > I now have what I need to put together my select statement. I now "get" > the database to a much greater extent than before. > > Thanks again, > Martha > > On Wednesday, October 2, 2019 at 10:21:35 PM UTC-7, Martha S wrote: >> >> Thanks, Adam, >> >> That should get me the last bit of the way. >> >> Martha >> >> >> >> On Wednesday, October 2, 2019 at 4:49:01 PM UTC-7, Adam Lodge wrote: >>> >>> Martha, >>> >>> I wish I could help with understanding why json export is working and >>> csv isn’t. Unfortunately, I can’t. That said, toward working with your >>> json output, you can identify the uuid of nodes/fields that store geometry >>> with this sql: >>> >>> select >>> b.name as model_name, >>> a.name as node_name, >>> a.nodeid >>> FROM nodes a >>> join graphs b on a.graphid = b.graphid >>> where >>> datatype = 'geojson-feature-collection' >>> order by b.name, a.name >>> >>> With that information, you can search for that uuid in your json to >>> isolate where geometry is stored in the json. >>> >>> --- >>> Adam Lodge >>> Farallon Geographics >>> >>> >>> On Oct 2, 2019, at 4:24 PM, Martha S <[email protected]> wrote: >>> >>> One more bit of information: I was able to export the business data to >>> json. Naturally, I did not find '|' in the resulting file. >>> >>> If this export includes geographies (or lack thereof, which is what I'm >>> really after) for this resource model, I might be able to query what I need >>> based on the tools you have already provided me here and export that to CSV. >>> >>> I'm still interested in any further guidance you might offer regarding >>> my inability to export business data to csv, even as I pursue this new >>> avenue. >>> >>> Thanks, >>> Martha >>> >>> >>> On Wednesday, October 2, 2019 at 3:37:45 PM UTC-7, Martha S wrote: >>>> >>>> Alas, Gentlemen, neither effort returned anything. In PGADMIN4, I ran >>>> both of the following with no results returned: >>>> >>>> SELECT >>>> >>>> a.resourceinstanceid, >>>> >>>> b.name as card_name >>>> >>>> FROM tiles a >>>> >>>> JOIN cards b on a.nodegroupid = b.nodegroupid >>>> >>>> WHERE 1=1 >>>> >>>> and tiledata::text like '%|%' >>>> >>>> >>>> set standard_conforming_strings=on; >>>> >>>> SELECT >>>> >>>> a.resourceinstanceid, >>>> >>>> b.name as card_name >>>> >>>> FROM tiles a >>>> >>>> JOIN cards b on a.nodegroupid = b.nodegroupid >>>> >>>> WHERE 1=1 >>>> >>>> and tiledata::text like E'%xa6%' >>>> >>>> >>>> In the python shell I ran the following with no results returned: >>>> >>>> >>>> from arches.app.models.tile import Tile >>>> tiles = Tile.objects.all() >>>> for tile in tiles: >>>> >>>> for value in tile.data.values(): >>>> >>>> if isinstance(value, unicode): >>>> if "|" in value: >>>> print tile.resourceinstance_id >>>> >>>> >>>> At least you are agreed, there is no there there. Just for grins, I >>>> reran the export business data command and got the same error as shown in >>>> the first post in this thread. As a final effort, I ran the following and >>>> got more than 10,000 results, since that's all that stayed in the terminal >>>> memory: >>>> >>>> >>>> from arches.app.models.tile import Tile >>>> >>>> tiles = Tile.objects.all() >>>> >>>> for tile in tiles: >>>> >>>> for value in tile.data.values(): >>>> >>>> if isinstance(value, unicode): >>>> >>>> print tile.resourceinstance_id, value >>>> >>>> >>>> Below is a partial list of the results. I don't see anything suspicious >>>> in the values, but I see that these few resources have multiple entries in >>>> this list. I don't know if that's because this list list is a complete dump >>>> of all 1M+ cards or if there's something to see here. I ran reports on a >>>> few of these and found nothing amiss. >>>> >>>> fd44f81a-14b1-41f9-b33a-d66c66a960ec >>>> 33595d18-c941-4a4b-91e8-0fa08e229c4d >>>> 52c5f05e-1827-4b06-909b-9a66ff3e6e95 >>>> 3db588f4-2384-4cbe-b429-5c63630b9b9d >>>> 52c5f05e-1827-4b06-909b-9a66ff3e6e95 138B177 25 >>>> 52c5f05e-1827-4b06-909b-9a66ff3e6e95 >>>> 08130495-9729-46fa-8a3b-2dfa97286ab9 >>>> 52c5f05e-1827-4b06-909b-9a66ff3e6e95 679152 >>>> fd44f81a-14b1-41f9-b33a-d66c66a960ec >>>> 0997ad7a-ad94-43b3-b662-8945c6c1b10e >>>> fd44f81a-14b1-41f9-b33a-d66c66a960ec >>>> fd44f81a-14b1-41f9-b33a-d66c66a960ec >>>> 52c5f05e-1827-4b06-909b-9a66ff3e6e95 >>>> f5d15b7d-da92-4b63-82a1-8632b361e860 >>>> fd44f81a-14b1-41f9-b33a-d66c66a960ec >>>> 3db588f4-2384-4cbe-b429-5c63630b9b9d >>>> fd44f81a-14b1-41f9-b33a-d66c66a960ec 117B181 568 >>>> 99b6ae9f-a061-46f4-b69c-f6f4b43ad69a 6137 E GARRISON DR >>>> 99b6ae9f-a061-46f4-b69c-f6f4b43ad69a >>>> 209c5206-eb2f-406e-b42e-288f84680a4a >>>> 52c5f05e-1827-4b06-909b-9a66ff3e6e95 411 N SPAULDING AVE >>>> 52c5f05e-1827-4b06-909b-9a66ff3e6e95 >>>> 209c5206-eb2f-406e-b42e-288f84680a4a >>>> fd44f81a-14b1-41f9-b33a-d66c66a960ec 3939 S URSULA AVE >>>> fd44f81a-14b1-41f9-b33a-d66c66a960ec >>>> 209c5206-eb2f-406e-b42e-288f84680a4a >>>> 29a5a1ce-bfd1-4f8a-90a8-41e76b337558 2608 S ORCHARD AVE >>>> 29a5a1ce-bfd1-4f8a-90a8-41e76b337558 >>>> ad7fce52-1185-4ad8-bc65-e100de19be96 >>>> 52c5f05e-1827-4b06-909b-9a66ff3e6e95 Orange Grove Avenue-Gardner Street >>>> Multi-Family Residential Historic District - Contributor >>>> 52c5f05e-1827-4b06-909b-9a66ff3e6e95 >>>> efd0fd4d-ebcf-4e7d-801f-0dc036a0c1b3 >>>> fd44f81a-14b1-41f9-b33a-d66c66a960ec Baldwin Plaza-Sunset Fields >>>> Historic District - Contributor >>>> fd44f81a-14b1-41f9-b33a-d66c66a960ec >>>> efd0fd4d-ebcf-4e7d-801f-0dc036a0c1b3 >>>> >>>> So, is there a middle ground here, where I can get more than nothing >>>> and less than all? Is there a way for me to log the bad records and >>>> continue with the export nonetheless? >>>> >>>> Somehow, I need to correct the issues and successfully export data from >>>> the database. >>>> >>>> Thanks in advance, >>>> Martha >>>> >>>> >>>> On Wednesday, October 2, 2019 at 10:40:31 AM UTC-7, Martha S wrote: >>>>> >>>>> Thank you,to both Adams. >>>>> >>>>> I will run both suggestions. Both are helpful to my ongoing database >>>>> activities. The more approaches in my toolbox, the better. >>>>> >>>>> It is great to know that the cards can be treated as flat files with >>>>> tiledata; I missed that somehow. Iterating through each record with the >>>>> ORM >>>>> approach in the python shell is a new entry point for me. >>>>> >>>>> I shall report back later today. >>>>> >>>>> Martha >>>>> >>>>> >>>>> >>>>> On Tuesday, October 1, 2019 at 10:57:40 PM UTC-7, Adam Cox wrote: >>>>>> >>>>>> Another approach would be to use the django ORM in the python shell. >>>>>> >>>>>> From your project run >>>>>> >>>>>> python manage.py shell >>>>>> >>>>>> Now you can paste this code in >>>>>> >>>>>> from arches.app.models.tile import Tile >>>>>> tiles = Tile.objects.all() >>>>>> for tile in tiles: >>>>>> for value in tile.data.values(): >>>>>> if isinstance(value, unicode): >>>>>> if "|" in value: >>>>>> print tile.resourceinstance_id >>>>>> print value >>>>>> >>>>>> Adam >>>>>> >>>>>> On Tue, Oct 1, 2019 at 11:26 PM Adam Lodge <[email protected]> wrote: >>>>>> >>>>>>> I realized that I inadvertently took this discussion offline to just >>>>>>> Martha and I. Here's some missing thread: >>>>>>> >>>>>>> Martha, >>>>>>> >>>>>>> (I think) you can issue this sql statement against the Postgres >>>>>>> database behind arches to identify the specific resource instance and >>>>>>> “card” that contains a given offending character: >>>>>>> >>>>>>> SELECT >>>>>>> a.resourceinstanceid, >>>>>>> b.name as card_name >>>>>>> FROM tiles a >>>>>>> JOIN cards b on a.nodegroupid = b.nodegroupid >>>>>>> WHERE 1=1 >>>>>>> and tiledata::text like '%|%’ >>>>>>> >>>>>>> Note that the offending character you search for will be defined on >>>>>>> the last line surrounded by wildcards. >>>>>>> >>>>>>> With the resourceinstanceid value, you can construct a url that will >>>>>>> take you strait to the resource editor for that specific resource >>>>>>> instance… >>>>>>> like this: >>>>>>> https://[hostname]/resource/[resourceinstanceid] >>>>>>> >>>>>>> You can use the card value to navigate to the specific card (or >>>>>>> form) that has the field with the offending value. >>>>>>> >>>>>>> It’s a kinda manual approach to fixing them, but at least you can >>>>>>> sniff them out with this approach. >>>>>>> >>>>>>> Best, >>>>>>> Adam >>>>>>> >>>>>>> --- >>>>>>> Adam Lodge >>>>>>> Geospatial Systems Consultant >>>>>>> Farallon Geographics >>>>>>> >>>>>>> On Oct 1, 2019, at 7:10 PM, Martha Selig <[email protected]> >>>>>>> wrote: >>>>>>> >>>>>>> Adam, >>>>>>> >>>>>>> I'm talking about the Arches database, though I am open to any >>>>>>> suggestion as to simplifying this task. I am unable to export to CSV >>>>>>> because of this symbol, but a flat file would definitely be the easiest >>>>>>> way >>>>>>> to look for and replace/delete it no matter where it is found.I would >>>>>>> like >>>>>>> to deal with this issue one time, if at all possible. We're starting a >>>>>>> cycle of reviewing, updating, and correcting data and I'm stuck at the >>>>>>> starting gate. >>>>>>> >>>>>>> Tomorrow I'm going to try to query just the records needed for the >>>>>>> first pass -- resources that don't have geographies defined -- and see >>>>>>> if I >>>>>>> can export those somehow. If I'm lucky, none of the records I need will >>>>>>> have '|' in them, so I can get a CSV. At least I can get the client >>>>>>> going. >>>>>>> >>>>>>> Any help would be greatly appreciated. That would include, I >>>>>>> suppose, some info on modifying the export code to skip to the next >>>>>>> entry >>>>>>> when this error is triggered. I haven't taken a look at the code to see >>>>>>> how >>>>>>> straightforward that might be, but if I could log the problem record and >>>>>>> keep on going, that would be swell. I wasn't thinking I'd be altering >>>>>>> core >>>>>>> code but if that keeps me going, why not? >>>>>>> >>>>>>> Thanks, >>>>>>> Martha >>>>>>> >>>>>>> On Tue, Oct 1, 2019 at 5:59 PM Adam Lodge <[email protected]> wrote: >>>>>>> >>>>>>>> Are you searching an Arches database, or just table in a given >>>>>>>> RDBMS, or a flat file of some sort? >>>>>>>> >>>>>>>> --- >>>>>>>> Adam Lodge >>>>>>>> >>>>>>>> On Oct 1, 2019, at 2:49 PM, Martha S <[email protected]> wrote: >>>>>>>> >>>>>>>> Do anyone have a recommendation for the best way to go through the >>>>>>>> database of >110,000 records to find all instances of the '|' in any >>>>>>>> field >>>>>>>> it might occur? I am told this was the delimiter used in .arches >>>>>>>> files, so >>>>>>>> there could be other instances sprinkled throughout the database. >>>>>>>> >>>>>>>> Needle in a haystack time. >>>>>>>> >>>>>>>> Thanks, >>>>>>>> Martha >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> On Tuesday, October 1, 2019 at 2:49:10 PM UTC-7, Martha S wrote: >>>>>>>> >>>>>>>> Do anyone have a recommendation for the best way to go through the >>>>>>>> database of >110,000 records to find all instances of the '|' in any >>>>>>>> field >>>>>>>> it might occur? I am told this was the delimiter used in .arches >>>>>>>> files, so >>>>>>>> there could be other instances sprinkled throughout the database. >>>>>>>> >>>>>>>> Needle in a haystack time. >>>>>>>> >>>>>>>> Thanks, >>>>>>>> Martha >>>>>>>> >>>>>>>> On Tuesday, October 1, 2019 at 10:35:47 AM UTC-7, Martha S wrote: >>>>>>>>> >>>>>>>>> Thank you, Alexi, >>>>>>>>> >>>>>>>>> I'll just have to hope that's the only "special" character in the >>>>>>>>> data. >>>>>>>>> >>>>>>>>> Martha >>>>>>>>> >>>>>>>>> On Friday, September 27, 2019 at 5:13:59 PM UTC-7, Martha S wrote: >>>>>>>>>> >>>>>>>>>> I am trying to export all the data for a particular resource >>>>>>>>>> model to CSV for review and modification and ran into an error >>>>>>>>>> during the >>>>>>>>>> process -- UnicodeEncodeError: 'ascii' codec can't encode >>>>>>>>>> character u'\xa6' in position 51: ordinal not in range(128) >>>>>>>>>> >>>>>>>>>> *My command* >>>>>>>>>> python manage.py packages -o export_business_data -d >>>>>>>>>> '/hpladata/Projects/Downloads/Historic District Mapping Files' >>>>>>>>>> -f 'csv' -c '/hpladata/Projects/Downloads/Historic District >>>>>>>>>> Mapping Files/Historic District.mapping' >>>>>>>>>> >>>>>>>>>> *Here's the full error dump* >>>>>>>>>> operation: export_business_data >>>>>>>>>> Traceback (most recent call last): >>>>>>>>>> File "manage.py", line 29, in <module> >>>>>>>>>> execute_from_command_line(sys.argv) >>>>>>>>>> File >>>>>>>>>> "/usr/local/lib/python2.7/dist-packages/django/core/management/__init__.py", >>>>>>>>>> line 364, in execute_from_command_line >>>>>>>>>> utility.execute() >>>>>>>>>> File >>>>>>>>>> "/usr/local/lib/python2.7/dist-packages/django/core/management/__init__.py", >>>>>>>>>> line 356, in execute >>>>>>>>>> self.fetch_command(subcommand).run_from_argv(self.argv) >>>>>>>>>> File >>>>>>>>>> "/usr/local/lib/python2.7/dist-packages/django/core/management/base.py", >>>>>>>>>> line 283, in run_from_argv >>>>>>>>>> self.execute(*args, **cmd_options) >>>>>>>>>> File >>>>>>>>>> "/usr/local/lib/python2.7/dist-packages/django/core/management/base.py", >>>>>>>>>> line 330, in execute >>>>>>>>>> output = self.handle(*args, **options) >>>>>>>>>> File "/Projects/prod/arches/arches/ >>>>>>>>>> management/commands/packages.py", line 190, in handle >>>>>>>>>> self.export_business_data(options['dest_dir'], >>>>>>>>>> options['format'], options['config_file'], options['graphs'], >>>>>>>>>> options['single_file']) >>>>>>>>>> File "/Projects/prod/arches/arches/ >>>>>>>>>> management/commands/packages.py", line 770, in >>>>>>>>>> export_business_data >>>>>>>>>> data = resource_exporter.export(graph_id=graph, >>>>>>>>>> resourceinstanceids=None) >>>>>>>>>> File >>>>>>>>>> "/Projects/prod/arches/arches/app/utils/data_management/resources/exporter.py", >>>>>>>>>> line 37, in export >>>>>>>>>> resources = self.writer.write_resources(graph_id=graph_id, >>>>>>>>>> resourceinstanceids=resourceinstanceids) >>>>>>>>>> File >>>>>>>>>> "/Projects/prod/arches/arches/app/utils/data_management/resources/formats/csvfile.py", >>>>>>>>>> line 194, in write_resources >>>>>>>>>> csvs_for_export = csvs_for_export + >>>>>>>>>> self.write_resource_relations(file_name=self.file_name) >>>>>>>>>> File >>>>>>>>>> "/Projects/prod/arches/arches/app/utils/data_management/resources/formats/csvfile.py", >>>>>>>>>> line 215, in write_resource_relations >>>>>>>>>> csvwriter.writerow({k:str(v) for k,v in relation.items()}) >>>>>>>>>> File >>>>>>>>>> "/Projects/prod/arches/arches/app/utils/data_management/resources/formats/csvfile.py", >>>>>>>>>> line 215, in <dictcomp> >>>>>>>>>> csvwriter.writerow({k:str(v) for k,v in relation.items()}) >>>>>>>>>> UnicodeEncodeError: 'ascii' codec can't encode character u'\xa6' >>>>>>>>>> in position 51: ordinal not in range(128) >>>>>>>>>> >>>>>>>>>> Any suggestions? >>>>>>>>>> >>>>>>>>>> Thanks, >>>>>>>>>> Martha >>>>>>>>>> >>>>>>>>> >>>>>>> -- >>>>>>> -- To post, send email to [email protected]. To >>>>>>> unsubscribe, send email to [email protected]. For more >>>>>>> information, visit >>>>>>> https://groups.google.com/d/forum/archesproject?hl=en >>>>>>> --- >>>>>>> You received this message because you are subscribed to the Google >>>>>>> Groups "Arches Project" group. >>>>>>> To unsubscribe from this group and stop receiving emails from it, >>>>>>> send an email to [email protected]. >>>>>>> To view this discussion on the web visit >>>>>>> https://groups.google.com/d/msgid/archesproject/412ef4fb-306b-4961-93f4-f9164065caaa%40googlegroups.com >>>>>>> <https://groups.google.com/d/msgid/archesproject/412ef4fb-306b-4961-93f4-f9164065caaa%40googlegroups.com?utm_medium=email&utm_source=footer> >>>>>>> . >>>>>>> >>>>>> >>> -- >>> -- To post, send email to [email protected]. To unsubscribe, >>> send email to [email protected]. For more information, visit >>> https://groups.google.com/d/forum/archesproject?hl=en >>> --- >>> You received this message because you are subscribed to the Google >>> Groups "Arches Project" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to [email protected]. >>> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/archesproject/d5d0ea82-d653-4112-848a-0a6f02437e3c%40googlegroups.com >>> <https://groups.google.com/d/msgid/archesproject/d5d0ea82-d653-4112-848a-0a6f02437e3c%40googlegroups.com?utm_medium=email&utm_source=footer> >>> . >>> >>> >>> -- > -- To post, send email to [email protected]. To unsubscribe, > send email to [email protected]. For more > information, visit https://groups.google.com/d/forum/archesproject?hl=en > --- > You received this message because you are subscribed to the Google Groups > "Arches Project" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/archesproject/edc306e0-5659-4fec-b0c4-5aaaae8e103d%40googlegroups.com > <https://groups.google.com/d/msgid/archesproject/edc306e0-5659-4fec-b0c4-5aaaae8e103d%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- -- To post, send email to [email protected]. To unsubscribe, send email to [email protected]. For more information, visit https://groups.google.com/d/forum/archesproject?hl=en --- You received this message because you are subscribed to the Google Groups "Arches Project" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/archesproject/CA%2BZLqy8WU86gK5Jf1NLpaWrJSNXhNOPLAVXNT-OvP%2B2ViPUM4Q%40mail.gmail.com.
